Range in VBA Outperforms

Question

Can someone please help me understand the code below.

Set items = Range("A2:A" & Range("A1").End(xlDown).Row) 

I don’t understand how the range is configured? What area is covered by A2:A and A1 in accordance with this? I know if I have a table like below this code works

But if I changed the location of the table to find out how to change the example code?

Answer 1

Here the code is divided into parts, and in the message boxes you can see what is happening, HTH.

 Sub test() ' Range.End Property: ' Returns a Range object that represents the cell at the end of the region that contains the source range. ' Equivalent to pressing END+DOWN ARROW. Dim endXlDown As Range Set endXlDown = Range("A1").End(xlDown) MsgBox "endXlDown.Address = " & endXlDown.Address Dim endXlDownRow As Long endXlDownRow = endXlDown.Row MsgBox "endXlDown.Row = " & endXlDown.Row Dim targetAddress As String targetAddress = "A2:A" & endXlDownRow MsgBox "targetAddress = " & targetAddress Dim items As Range Set items = Range(targetAddress) MsgBox "items.Address = " & items.Address items.Select ' and here you see the resulting range as selection End Sub 

Answer 2

What area is covered by A2:A and A1 in accordance with this?

The code does not attempt to cover A2:A and A1 .

In this code:

 Set items = Range("A2:A" & Range("A1").End(xlDown).Row) 

Component "A2:A" & Range("A1").End(xlDown).Row combined as a single Range() parameter:

• A2:A - String ;
• & - String concatenation operator ;
• Range("A1") selects A1 ;
• Range("A1").End(xlDown) selects to the last cell "region" in A , starting from A1 (in this case, A4 );
• Range("A1").End(xlDown).Row returns the selected row number of the cell (in this case 4);

Thus, the whole part ends as "A2:A" & "4" or A2:A4 .

Given this, for your second screenshot, you can go with a similar process:

• C5:C selects from C5 (which is the first “cell” data cell);
• & concatenates the string;
• Range("C4") selects C4 (which is the "title" cell of the "Item" column);
• Range("C4").End(xlDown) is selected to the last non-empty cell in C starting with C4 ;
• Range("C4").End(xlDown).Row returns the number of the selected cell (in this case 7);

So, for the second screenshot you got:

 Set items = Range("C5:C" & Range("C4").End(xlDown).Row)