excel VBA - Finding variables in tables (last row, last column, cell address, etc)

RCBricker

Well-known Member
Joined
Feb 4, 2003
Messages
1,560
ok so I have some data sent over to me in tables. I need to run some automated activities on these tables. I have found that my trusty old last row and last column functions are not reading the data in the tables.

Trusty Last column code
Code:
Public Function LASTCOL(ws As Worksheet)
Dim rngLCOL As Range
Dim lngCOLS As Long

    ws.Select
    With ws
        Set rngLCOL = .Cells
On Error Resume Next
        lngCOLS = rngLCOL.Find(What:="*", _
                after:=rngLCOL.Cells(1), _
                LookAt:=xlPart, _
                LookIn:=xlFormulas, _
                SearchOrder:=xlByColumns, _
                SearchDirection:=xlPrevious, _
                MatchCase:=False).Column
    End With
On Error GoTo 0
    If Err > 0 Then
        lngCOLS = 1
    End If
    LASTCOL = lngCOLS
End Function

this returns a value of zero (0) when the data is in a table. I have no idea why. I can use the other simple last column code

Code:
lastCOL=ws.cells(1,ws.columns.count).end(xltoleft).column

this requires that the code can get the first row or first column of the databodyrange. This is where I am having issues.

I need to either amend the first code so that I can use old trusty with tables, or I need to understand how to get the first row's position on the worksheet (so if the table starts at A6, then the row of the first row is 6).

I'm a little lost here.

thanks,
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,770
Office Version
  1. 2010
Platform
  1. Windows
This worked fine for me regardless of whether the table starts in the first row:

Code:
Public Function LASTCOL(ws As Worksheet) As Long
  Dim cell          As Range

  Set cell = ws.Cells.Find(What:="*", _
                           after:=ws.Cells(1), _
                           LookAt:=xlPart, _
                           LookIn:=xlFormulas, _
                           SearchOrder:=xlByColumns, _
                           SearchDirection:=xlPrevious, _
                           MatchCase:=False)
  If cell Is Nothing Then LASTCOL = 1 Else LASTCOL = cell.Column
End Function
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,323
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
If you just want last column or last row for the Table, regardless of what else might be on the sheet, one way is to assign a range object to represent the Table. Change the Table name (in bold red font below) to suit.
Rich (BB code):
Sub NavigateTable()
Dim Table1 As Range
'set the range object for the Table
Set Table1 = Range("Table1").Offset(-1, 0).Resize(Range("Table1").Rows.Count)
'or do it by using the list object
'Set Table1 = ActiveSheet.ListObjects("Table1").Range
'Determine what row the Table headers are on
MsgBox Table1.Rows(1).Row
'determine what row is the last row of the table
MsgBox Table1.Rows(Table1.Rows.Count).Row
'determine what column is the first column of the table
MsgBox Table1.Columns(1).Column
'determine what column is the last column of the table
MsgBox Table1.Columns(Table1.Columns.Count).Column
End Sub
 

RCBricker

Well-known Member
Joined
Feb 4, 2003
Messages
1,560
If you just want last column or last row for the Table, regardless of what else might be on the sheet, one way is to assign a range object to represent the Table. Change the Table name (in bold red font below) to suit.
Rich (BB code):
Sub NavigateTable()
Dim Table1 As Range
'set the range object for the Table
Set Table1 = Range("Table1").Offset(-1, 0).Resize(Range("Table1").Rows.Count)
'or do it by using the list object
'Set Table1 = ActiveSheet.ListObjects("Table1").Range
'Determine what row the Table headers are on
MsgBox Table1.Rows(1).Row
'determine what row is the last row of the table
MsgBox Table1.Rows(Table1.Rows.Count).Row
'determine what column is the first column of the table
MsgBox Table1.Columns(1).Column
'determine what column is the last column of the table
MsgBox Table1.Columns(Table1.Columns.Count).Column
End Sub

Thanks guys. Joe I think your example has cleared up my confusion. I appreciate the help seeing the forest through the trees.
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,323
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Thanks guys. Joe I think your example has cleared up my confusion. I appreciate the help seeing the forest through the trees.
You are welcome - thanks for the reply.
 

RCBricker

Well-known Member
Joined
Feb 4, 2003
Messages
1,560
You are welcome - thanks for the reply.

I hate it when people don't reply to threads. I do forget sometime, but I try to let everyone know when I either have my answer or the guidance has helped enough that I think I can work it on my own.
 

Watch MrExcel Video

Forum statistics

Threads
1,112,816
Messages
5,542,660
Members
410,567
Latest member
SCraig123
Top