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,
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,910
Messages
6,175,316
Members
452,634
Latest member
cpostell

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top