Finding last used row in non-contingous data using column number (not letter)

[RawlinsCross]

So this is the oldest (or one of them) trick in the book with

lRow = ThisWorkbook.Worksheet("Sheet1").Range("A" & Rows.Count).End(xlUp).Row

But what I'm working with is a column number (say 15). Can I use the number 15 directly and try to look for the last row containing data? Or do I need to convert it to a letter first?

John

 

[DanteAmor]

Try this

lRow = ThisWorkbook.Worksheet("Sheet1").Cells(Rows.Count, 15).End(xlUp).Row

 

[MARK858]

Or maybe

lRow = ThisWorkbook.Worksheets("Sheet1").Columns(15).Find("*", , xlValues, , xlByRows, xlPrevious).Row

Please note the s at the end of Worksheet

 

[DanteAmor]

Mark's appreciation is correct, I also forgot the "s" in worksheets ?‍

  lRow = ThisWorkbook.Worksheets("Sheet1").Cells(Rows.Count, 15).End(xlUp).Row

 

[RawlinsCross]

Right… duh! You just need to get to the last cell of that particular column and then xlUp! Cheers.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[Peter_SSs]

Right… duh! You just need to get to the last cell of that particular column and then xlUp! Cheers.

Correct. That is what your original code was doing too of course, just using Range() instead of Cells()

Note that Cells() is quite flexible as you can use numbers or letters for the column. These would both do the same job

lRow = ThisWorkbook.Worksheets("Sheet1").Cells(Rows.Count, 15).End(xlUp).Row
lRow = ThisWorkbook.Worksheets("Sheet1").Cells(Rows.Count, "O").End(xlUp).Row

 

[RawlinsCross]

Hey Peter, yeah, I get very confused with Range.Cells and Worksheets.Cells with how you can use cells with both Range and Worksheets. Another source of confusion is Range within Range. I doubt it's too complicated but sometimes one forgets how to spell the word "the". I suspect it'll click eventually and become self-evident. In the meanwhile, if there's a resource on all this that you can recommend, I'd certainly appreciate it.

John

 

[MARK858]

Referring to Ranges in VBA

There are two main properties you can use to obtain a Range object reference in VBA: Range and Cells. Range The Range property will accept one or two arguments, with the second one being optional. …

excelmatters.com

 

[RawlinsCross]

Good reference Mark. Thanks for the tip.