I have code like this:

Range("A10" , "A" & Cells(Rows.Count, 1).End(xlUp).Row) 

Could someone explain to me how exactly does it work? Why do we concatenate "A" with Cells(Rows.Count, 1)? What range that will be? "A", (1048576, 1) - it doesn't make sense for me

CodePudding user response：

So the idea of that line is "Find the range starting from 'A10' until the last non-blank cell in column A", which would be something like "A10:A50", if "A50" were the last cell.

So how do you find that range? There are many tools like Worksheet.UsedRange or Range.CurrentRegion. But those can be unreliable because they will be influenced by things outside of column "A" and will not be as direct as specifically searching for the last cell in column A.

That's why you'll often see VBA coders use Range.End. So from "A10", we could do [A10].End(xlDown) but that runs into issues if there are any blank cells inbetween "A10" and the last cell. Because End stops at the next blank cell when starting on a non-blank cell or at the next non-blank cell when starting on a blank cell.

So to find the last cell in column "A" guaranteed, without other factors blocking you or causing mis-sized ranges, you want to start at the bottom of the page and move upwards to find the lowest non-blank cell

There are many way to write that same line thanks to VBA having such flexible objects.

Cells(Rows.Count, "A").End(xlUp)

Columns(1).Rows(Rows.Count).End(xlUp)

Range("A:A").Cells(Rows.Count).End(xlUp)

Rows(Rows.Count).Cells(1).End(xlUp)

Once you have that last cell, you just need to create a range that stretches from "A10" to that last cell. You can quickly accomplish this with Range(Cell1,Cell2) where Cell1 is "A10" and Cell2 is that last cell.

You line does this in a roundabout fashion of finding the row # of the last cell, and then appending that onto "A", creating a String argument like "A50". It could have just used the last cell as the argument as a range object.

Range("A10" , Cells(Rows.Count, 1).End(xlUp))