Go to last Cell of Current Table

[SanjayGMusafir]

Hi Experts

I have multiple tables on one sheet. The following Macro works fine on the first table of the sheet. But when it comes to using it on the any other table of the sheet, it misfires.

Please help
Thanks a lot

Sub GoTableLast()
'
' GoTableLast Macro
'

'
    Call GoHome
    
    'Range(ActiveCell, ActiveCell.End(xlToRight)).Select
    ActiveCell.Offset(-1, 0).End(xlToRight).Select
    'ActiveCell.End(xlDown).Select
    
    Dim LRow As Long, LCol As Long
    
    LCol = ActiveCell.Column
    
    
    'To get last row ignoring blank rows in a column
    LRow = Columns(LCol).Find("*", SearchDirection:=xlPrevious, _
    SearchOrder:=xlByRows, LookIn:=xlValues).Row
    
    'To go to last active row of the same Active column
    'Cells(LRow, LCol).Select
    Cells(LRow, LCol).End(xlDown).End(xlDown).End(xlUp).Offset(0, -1).Select
        
End Sub

 

[iggydarsa]

What does GoHome do? If it always goes back to a certain cell the code will only work for a specific table only.

 

[SanjayGMusafir]

What does GoHome do? If it always goes back to a certain cell the code will only work for a specific table only.

It goes to Cell just after Freezed Cells - Like Ctrl Home in Windows

 

[SanjayGMusafir]

@iggydarsa I actually tried thinking of ways on how to tell excel to find last row and last column of the current table. But my knowledge with VBA is not even to the level of a novice . I only do hit or miss things.

The current vba was working fine because going to ctrl+Home and then one row up would take it to the headers row of first table.

But when it comes to a table somewhere else in the sheet, I went blank

If you may help

Thanks a lot

 

[SanjayGMusafir]

As I was explaining things in the previous post #4, an idea struck my brain. And I worked as below.

It works most of the times. But there is a glitch. If the current column has some blank cell (which at time shall be) then it jumps to the wrong cell at bottom. And at times it could be far-far away from the last column.

Please help

The macro I evolved is as below

Sub GoTableLast()
'
' GoTableLast Macro
'

'
    'Call GoHome
    
    'Range(ActiveCell, ActiveCell.End(xlToRight)).Select
    'ActiveCell.Offset(-1, 0).End(xlToRight).Select
    'ActiveCell.End(xlDown).Select
    
    Dim LRow As Long, CCol As Long, LCol As Long
    
    CCol = ActiveCell.Column
    ActiveCell.End(xlUp).End(xlToRight).End(xlDown).Select
    LCol = ActiveCell.Column
    
    'To get last row ignoring blank rows in a column
    LRow = Columns(CCol).Find("*", SearchDirection:=xlPrevious, _
    SearchOrder:=xlByRows, LookIn:=xlValues).Row
    
    'To go to last active row of the same Active column
    'Cells(LRow, LCol).Select
    Cells(LRow, LCol).End(xlDown).End(xlDown).End(xlUp).Offset(0, -1).Select
        
End Sub

 

[Alex Blakenburg]

Can you clarify what you are actually trying to find ?
In the image below with the ActiveCell being in Table2, do you want:
G20, H20 or H22

 

[Fuji]

Perhaps

Sub GoTableLast()
    Dim tbl As ListObject, x
    For Each tbl In ActiveSheet.ListObjects
        If Not Intersect(ActiveCell, tbl.Range) Is Nothing Then
            x = tbl.Parent.Evaluate("max(if(" & tbl.Range.Address & "<>"""",row(1:" & tbl.ListRows.Count + 1 & ")))")
            tbl.Range(x, 1).Select: Exit For
        End If
    Next
End Sub

 

[Alex Blakenburg]

Based on

'To go to last active row of the same Active column

Here is another option:

Sub GoToLastRowInColumnActiveTable()

    Dim tblActive As ListObject
    Dim rCol As Range, rLastCell As Range
    
    Set tblActive = ActiveCell.ListObject
    If tblActive Is Nothing Then Exit Sub
    
    Set rCol = Intersect(ActiveCell.EntireColumn, tblActive.Range)

    Set rLastCell = rCol.Find(What:="*", after:=rCol.Cells(1), SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
        
    rLastCell.Select
    
End Sub

 

[Fuji]

'To go to last active row of the same Active column

Missed this...

Sub GoTableLast()
    Dim tbl As ListObject, x
    For Each tbl In ActiveSheet.ListObjects
        If Not Intersect(ActiveCell, tbl.Range) Is Nothing Then
            x = tbl.Parent.Evaluate("max(if(" & tbl.Range.Address & "<>"""",row(1:" & tbl.ListRows.Count + 1 & ")))")
            tbl.Range(x, ActiveCell.Column - tbl.Range(1).Column + 1).Select: Exit For
        End If
    Next
End Sub

 

[SanjayGMusafir]

Can you clarify what you are actually trying to find ?
In the image below with the ActiveCell being in Table2, do you want:
G20, H20 or H22


View attachment 120486

@Alex Blakenburg in your example I would like to go to cell H22.

I have few more replies, I haven't seen them yet. I shall be checking them now.