Question on Range() syntax in VBA

[armando12345]

Hi all,

Basically I just want to know what this line does and it's tough to google.

Please Login or Register  to view this content.

I understand that it's a For loop that will look through each cell in the b3:end of data

However, I don't really understand the syntax of the Range() part. Can someone explain why the & is used and how that fits in? Thanks!

[jeffreybrown]

The part after the & is grabbing the end of column B so it can build the range ... B3:B100. (That's just an example. I don't know that 100 is the end)

[armando12345]

So it's concatinating the b3:b and the row number where the last piece of data is found?

[Rick Rothstein]

So it's concatinating the b3:b and the row number where the last piece of data is found?

Yes, sort of... it is the row number for the last piece of data in Column B (which is not necessarily the last piece of data on the entire worksheet).

I would also note that this line of code can be written without using concatenation by just giving the Range object a starting and stopping cell...

For Each cell In Range("B1", Cells(Rows.Count, "B").End(xlup))

[armando12345]

Makes sense that you could write it how you did...

That basically takes "B1" as the starting cell in the range, and the second cell in the range is Cells(lastrowinfile, in column b) then xlup which is basically ctrl + up arrow.

Am I getting that right?

[Rick Rothstein]

Yes... where the CTRL+UP Arrow is performed from the last cell in the column.