Using VBA to select the last row of a dynamic table

[floragray]

Hi All,

I have a table(not pivot table but a regular table) in Excel 2010 that is connected to a database and is constantly refreshing data.
I need to apply a filter that somehow gives me the last row of data, whichever that is.
I'm not soo good at VBA - could someone please help me out?

Thanks a ton

 

[Michael M]

If you only want the last row to be active, you don't need a filter
try

Sub MM1()
Dim lr As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row
Rows(lr).Activate
End Sub

If you only want to know the last row number use this one

Sub MM2()
Dim lr As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row
MsgBox "Last row is " & lr
End Sub

 

[floragray]

If you only want the last row to be active, you don't need a filter
try

Sub MM1()
Dim lr As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row
Rows(lr).Activate
End Sub

If you only want to know the last row number use this one

Sub MM2()
Dim lr As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row
MsgBox "Last row is " & lr
End Sub

Hi Michael

I appreciate the speedy response!
I tried running it, but it dzn work:



Sub Showlastrow()




Dim lr As Long


Sheets("ABC Report").Select
lr = ActiveSheet.ListObjects("Table_Default__STG1_ABC_REP_SOURCE").Cells(Rows.Count, 1).End(xlUp).Row


Rows(lr).Activate


End Sub



Also i'm not sure what you mean by activate.

Will this piece of code only show me the last row of the table on this page, cuz thats what i need...

 

[Michael M]

Both of my codes will identify the last row (lr) either by msgbox or by activating that row
The sheet needs to be the activesheet and the code needs to be in either a standard module or the "This Workbook" module

 

[floragray]

Both of my codes will identify the last row (lr) either by msgbox or by activating that row
The sheet needs to be the activesheet and the code needs to be in either a standard module or the "This Workbook" module

Hey Michael, thanks again.

But this code doesnt fulfill my requitement.
I have a table with multiple rows and it gets updated everyday with an additional row.

I need a filtering option, wherein at the click of a button, all rows except the very last one get hidden
So basically - its like using the dropdown filter and desecting all except the very last row of the table.
Its a regular table not a pivot table.

Really appreciate your help.

 

[Michael M]

Don't have Excel at the mo', but try

Sub Showlastrow()
Dim lr As Long
lr = Sheets("ABC Report").Range("Table_Default__STG1_ABC_REP_SOURCE").ListRows.Count
MsgBox "Last rows is " & Rows(lr)
End Sub

 

[Teeroy]

Hi Floragray, Michael's intial code will find you the last cell in the sheet in column A (and you could copy that data to a location where it is the only thing shown). It's parent is the worksheet object. From your most recent post it sounds like you prefer to start with the Table object. Try:

With ActiveSheet.ListObjects("Table_Default__STG1_ABC_REP_SOURCE").Range
    Range(.Cells(2, 1), .Cells(.Rows.Count, 1).Offset(-1, 0)).EntireRow.Hidden = True
End With

 

[Michael M]

Thanks Teeroy....I'm flying a bit blind at the moment...in the car and no excel....

 

[floragray]

Holy Awesome! This workssss :D:D:D:D:D
Thanks a tonn you guyssss!

 

[Teeroy]

You are welcome.

Michael, no excel... the horror . No email ray:.