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

 

[SanjayGMusafir]

Based on


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

@Alex Blakenburg Thanks for helping

With the above code, as per your example #6 it selects F18 while I want it to select H22.

 

[SanjayGMusafir]

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

@Fuji Thanks for helping and Sorry for the late response.

I didn't miss it. I was taking rest and saw it now.

The challenge with your code is that it takes me to the last row (with data) of the Current active column.

While I want it to go the the last cell of the current table. My table is likely to contain a lot of blank rows with some columns carrying formulae (that are only visible when some data is entered in the row) and some absolutely blank. If I may drop you an idea in post #6 example that @Alex Blakenburg gave, I would like to go to cell H22 when ActiveCell is in Table 2.

 

[Fuji]

Then something like...

Sub GoTableLast()
    Dim tbl As ListObject
    For Each tbl In ActiveSheet.ListObjects
        If Not Intersect(ActiveCell, tbl.Range) Is Nothing Then
            tbl.Range(tbl.Range.Rows.Count, ActiveCell.Column - tbl.Range(1).Column + 1).Select: Exit For
        End If
    Next
End Sub

 

[Alex Blakenburg]

You know that makes the column referencing irrelevant right ?!
(you don't actually need the set line)

Sub GoToLastCellActiveTable()

    Dim tblActive As ListObject
    Dim rLastCell As Range
   
    Set tblActive = ActiveCell.ListObject
    If tblActive Is Nothing Then Exit Sub

    With tblActive.Range
        Set rLastCell = .Cells(.Cells.Count)
        rLastCell.Select
    End With
   
End Sub

 

[SanjayGMusafir]

Then something like...

Sub GoTableLast()
    Dim tbl As ListObject
    For Each tbl In ActiveSheet.ListObjects
        If Not Intersect(ActiveCell, tbl.Range) Is Nothing Then
            tbl.Range(tbl.Range.Rows.Count, ActiveCell.Column - tbl.Range(1).Column + 1).Select: Exit For
        End If
    Next
End Sub

@Fuji This only takes me to the last row of current column

 

[SanjayGMusafir]

You know that makes the column referencing irrelevant right ?!
(you don't actually need the set line)

Sub GoToLastCellActiveTable()

    Dim tblActive As ListObject
    Dim rLastCell As Range
  
    Set tblActive = ActiveCell.ListObject
    If tblActive Is Nothing Then Exit Sub

    With tblActive.Range
        Set rLastCell = .Cells(.Cells.Count)
        rLastCell.Select
    End With
  
End Sub

@Alex Blakenburg it works exactly as intended.

Thanks a lot for your patience and help

 

[Fuji]

Then like my first post

  tbl.Range(tbl.Range.Rows.Count,1).Select: Exit For

 

[SanjayGMusafir]

Then like my first post

  tbl.Range(tbl.Range.Rows.Count,1).Select: Exit For

Your wish my command

Thanks for help and patience @Fuji