Range.Areas返回错误的行数

Question

我有一个VBA应用程序，从网站下载所有HTML表，并解析到一个工作表。然后我编写了代码，它将在A列的每个表的标题中找到一个字符串，激活该单元格，识别当前区域的范围，并将列表对象名称添加到工作表名称管理器中。我遇到的问题是代码行260，其中消息框显示表中的行数。即使表具有不同的行数，该数字也始终相同。我认为问题出在第210到250行代码中。我已经搜索了整个网络，包括堆栈溢出，都找不到解决方案。

有没有人知道为什么消息框中显示的数字不能反映表中的行数？

'---------------------------------------------------------------------------------------
' Method : test_currentregion_IncludeHeaders
' Author : Richard
' Date   : 10/4/2016
' Purpose: Find cell with value and turn into named table with headers
'---------------------------------------------------------------------------------------
Sub test_currentregion_IncludeHeaders()

10        On Error GoTo test_currentregion_IncludeHeaders_Error

          'convert all tables (listobjects) to ranges
          Dim WS As Worksheet, LO As ListObject
20        For Each WS In Worksheets
30            For Each LO In WS.ListObjects
40                LO.Unlist
50            Next
60        Next

          'find currentregions and add table
          Dim tbl As Object
          Dim c As Object
          Dim firstAddress As Variant
          Dim Hdr As String
          Dim rngTable As Range
          Dim rows As Long
          Dim Line As Variant
          Dim iCounter As Long

70        Hdr = "Header"
80        iCounter = 1
90        rows = 0

100       With ThisWorkbook.Worksheets(1).Range("A:A")
110           Set c = .Find(Hdr, LookIn:=xlValues)
120           If Not c Is Nothing Then
130               firstAddress = c.Address
140               c.Select        'must select object
150           End If

160       Do

170       With ThisWorkbook.Worksheets(1)
180           Set rngTable = c.CurrentRegion
190           .ListObjects.Add(SourceType:=xlSrcRange, Source:=rngTable, _
              xlListObjectHasHeaders:=xlYes, TableStyleName:="TableStyleMedium1") _
              .Name = "List" & iCounter
200       End With

210       With ThisWorkbook.Worksheets(1).ListObjects(1)
220           For Each Line In .Range.SpecialCells(xlCellTypeVisible).Areas
230               rows = rows + Line.rows.Count
240           Next
250       End With

260       MsgBox "Number of rows displayed = " & rows

          'reset selected variables
270           iCounter = iCounter + 1
280           rows = 0
290           Set Line = Nothing

          'find next currentregion
300           Set c = .FindNext(c)
310       Loop While Not c Is Nothing And c.Address <> firstAddress
320    End With

330       On Error GoTo 0
340       Exit Sub

test_currentregion_IncludeHeaders_Error:

350       MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure_test_currentregion_IncludeHeaders of Sub current_region"

End Sub

Answer 1

您遇到的问题是您总是引用第一个ListObject .ListObjects(1)。

您可以访问某个范围所属的Listobject，并返回其行数(不包括标题)，如下所示：

rows = rngTable.ListObject.DataBodyRange.rows.Count