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

[RCBricker]

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

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

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,

 

[shg]

This worked fine for me regardless of whether the table starts in the first row:

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]

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.

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]

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.

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]

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]

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.