Finding Last Row within Table

[cstimart]

I have a spreadsheet which contains a Table. I would like to find the bottom last cell in Column B that has a value...however,

LR = Range("A" & Rows.Count).End(xlup).Row

only tells me the bottom of the Table, but there are empty cells above that.

Is there an easy way around this?

 

[circledchicken]

Hi,

One option, try adding a second End(xlUp), i.e.

Range("A" & Rows.Count).End(xlup).End(xlUp).Row

Although this is probably a bad idea if they those empty cells get filled up. Maybe, if you don't have spaces in your data then:

Range("A1").End(xlDown).Row

 

[u0107]

Would "Countif(Range, "<>''") help?

Regards,

Uttam

 

[Jerry Sullivan]

Dim lRow As Long
With ActiveSheet.ListObjects("Table1")
    lRow = .ListColumns(2).DataBodyRange.Find(What:="*", _
            SearchDirection:=xlPrevious).Row
End With

If there is possibility that the Column is empty, you should add an errorhandler for that.

 

[niceguy_jay]

use this

LR = Range("B" & Rows.Count).End(xlup).Row</pre>

 

[cstimart]

use this

Actually, I was, my first post was a typo. Ooops!