当region选择太多时，使用vba和变量结束行和列创建表

Question

我有一个宏，可以在工作簿中的每个工作表中旋转，并为该工作表上的数据创建一个表，但每个工作表上的数据以不同的行和列结束。当使用。region时，它将包含比它应该包含的更多的行和列。当我总是知道结束列时，我的代码工作得很好，因为我可以使用lRow=Cells(rows.count,2).end(xlUp).row设置变量行，现在因为我不知道我保持的最大列，所以对象'_global'的'method'range‘失败了。这是我的宏，有人有什么想法吗？

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

Answer 1

类似于这样：

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