How to make a range variable in VBA?

I'm a newbie and I've got some code like this.

Dim rng As Range. Set rng = Sheets("Sheet1").Range("A1:E3") It works fine, but I wonder what is the difference between Set rng as Range and Dim rng As Range. If I don't make rng As Range, what happens to the variable? Set rng = Sheets("Sheet1").Range("A1:E3") This assigns the contents of A1:E3 to the variable rng. Set rng = Sheets("Sheet1").Range("A1:E3") This declares the variable rng as being of type Range. The difference is subtle, but important. Rng.Value = "Hello World" This changes the contents of the variable rng to "Hello World". Set rng = "Hello World". This creates a Range variable rng, whose Value property is set to "Hello World". The difference is how the variable is declared. When you declare the variable as Range you create a type and when you use Dim you're declaring the variable without any type.

A simple example can be seen below: Sub Sample(). Set rng = Sheets("Sheet1").Range("A1:E3") Debug.Print rng.Address '-->A1:E3
Debug.Cells(2) '-->A2 rng.Cells(2)

How to use range formula in VBA?

I have a scenario, where I need to copy specific data from another sheet using vba and pasting it on a new sheet.

I need to calculate the end row of the copied data, as i'm looking for a dynamic way of copying the data. For example, my original sheet has 2 columns. I will need to copy specific data from the cell A2 to cell K2 in my new sheet. After the copying, I need to paste it in A13(I'm not sure if it's working or not). I have tried this formula, but the last line is wrong. I don't know why.

Cells(x, 2).Select ActiveSheet.Range("A1:K" & Range(Cells(1, 2), Cells(Rows.Count, 2)).End(xlUp).Row)
Range("A13").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks :=False, Transpose:=True. I think, the range is wrong in my case. Can anyone tell me what I'm doing wrong here? Thanks. The syntax you are using is for copying and pasting based on value (not formula). You'll need to add the following. It can be anything from two up to the full length of your column you are copying from.

Cells(1, 2).Range("A1:K" & Range(Cells(1, 2), Cells(Rows.Count, 2)).End(xlUp).Row)
Range("A13").

How to set range value in VBA?

I was searching for this question, but couldn't find one that would help.

Let's say I have a table: A B C D. 1 0 1 0. 0 1 0 1. 1 0 1. How can I change the range of column D based on which cell contains A=1, or A=1 and B=0, or B=1 and A=0, etc. For example, If A=1 and B=1, Column D will be 1-3 and 5-7. And if B=0 and A=1, Column D will be 4-6.

So assuming your table starts at cell A1 then. Sub test(). Dim rw As Long. Rw = 1. For I = 2 To Cells(Rows.Count, "A").End(xlUp).Row
If Cells(i, "A").Value = 1 Then For j = 2 To 4. If Cells(i, "B").Value = 0 Then If Cells(i, "C").Value = 1 Then Cells(i, "D").Resize(rW).Value = 1
Cells(i, "D").Value = Cells(i, "D").Resize(rW).