Create table using vba and variable ending row and columns, when region selects too much

Question

I have a macro the spins through each worksheet in my workbook and creates a table for the data on that sheet, however the data on each sheet ends in a different row and column. When using the .Region it would include more rows and columns than it should. When i always knew the ending column my code worked fine as i could set the variable row using lRow = Cells(Rows.Count, 2).End(xlUp).Row, now since i don't know the max column i keep getting the 'Method 'Range' of object '_Global' failed. here is my macro, anyone have any ideas?

Dim TbName As String
Dim sh As Worksheet
Dim x As Integer
Dim lRow As Long
Dim lCol As Long
Dim s As String

For Each sh In ThisWorkbook.Worksheets


sh.Select

TbName = sh.Name & "_Tb"
'On Error Resume Next

            If IsEmpty(Range("B7").Value) = False Then
                    lRow = Cells(Rows.Count, 2).End(xlUp).Row 'find last row in set of data
                    lCol = Cells(7, Columns.Count).End(xlToLeft).Column
                    s = Range("B7", lRow & lCol).Address ' **this line does not work**
                     's = Range("B7", "D" &lrow).address ' **this line works**
                        sh.ListObjects.Add(xlSrcRange, Range(s), , xlYes).Name = _
                                TbName
                            Range(TbName).Select
                            sh.ListObjects(TbName).TableStyle = "TableStyleMedium3"
                Else
                        Application.DisplayAlerts = False
                        sh.Delete
            End If 


x = x + 1
    Next sh
    On Error GoTo 0
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub

Range("B7", Cells(lRow, lCol)) Consider not using Select but qualifying your range/cells calls with sh. Relying on a specific sheet being active when your code runs can cause hard-to-trace problems as your code evolves. — Tim Williams

Tim Williams Tim Williams · Accepted Answer · 2020-05-16T00:12:48

Something like this:

Sub Tester()

    Dim TbName As String
    Dim sh As Worksheet, rngTable As Range
    Dim x As Integer
    Dim lRow As Long
    Dim lCol As Long
    Dim s As String, lo As ListObject

    For Each sh In ThisWorkbook.Worksheets
        If Len(sh.Range("B7").Value) > 0 Then
            lRow = sh.Cells(sh.Rows.Count, 2).End(xlUp).Row
            lCol = sh.Cells(7, sh.Columns.Count).End(xlToLeft).Column
            Set rngTable = sh.Range("B7", sh.Cells(lRow, lCol))  '<<<<<<<<

            Set lo = sh.ListObjects.Add(xlSrcRange, rngTable, , xlYes)
            lo.Name = sh.Name & "_Tb"
            lo.TableStyle = "TableStyleMedium3"
        Else
            Application.DisplayAlerts = False
            sh.Delete
        End If
    Next sh

End Sub

Create table using vba and variable ending row and columns, when region selects too much

1 Answers