Excel 2010 - Last Row in a Table

[MrKowz]

Good afternoon everyone!

I'm having a bit of an issue working with Tables in Excel 2010 (first time using them, and first time using VBA with them as well). When the table is empty (contains only headers), my VBA last row statement is returning the row under the headers, not the header row.

For example, if my headers are in Row 1, and my table is blank, using the statement:

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

This returns 2, not the expected 1 that I would get if the sheet was not set up as a table.

However, when I populate the first row (ie row 2 now contains data), then the same statement above returns the expected 2. And when row n is populated, the statement returns the expected value of n.

Is there another method or declaration I need for the VBA code to properly return the "last row" of my table, even when the first data row is blank without doing some nasty workaround?

Thanks much!

~Keith

 

[Leith Ross]

Hello Keith,

If by Table you mean a List Object then the macro below will work. It has error handling in case there is no header. You will need to change the subscript to match your ListObject's index.

Dim R As Long
Dim Rng As Range

    On Error Resume Next
    Set Rng = ActiveSheet.ListObjects(1).Range
    
    If Err = 0 Then
       R = Rng.Find("*", , xlValues, xlWhole, xlByRows, xlPrevious, False).Row
    Else
       R = 0
    End If

 

[MrKowz]

Thanks Leith, I'll give this a shot in a tick.

 

[Marcelo Branco]

Hello Keith,

If by Table you mean a List Object then the macro below will work. It has error handling in case there is no header. You will need to change the subscript to match your ListObject's index.

Dim R As Long
Dim Rng As Range
 
    On Error Resume Next
    Set Rng = ActiveSheet.ListObjects(1).Range
 
    If Err = 0 Then
       R = Rng.Find("*", , xlValues, xlWhole, xlByRows, xlPrevious, False).Row
    Else
       R = 0
    End If

Thank you for sharing this code. It worked perfect for me.

M.