vba - Excel VBA查找过滤范围的最后一行

Question

我正在尝试在工作表上查找过滤范围的行数。 LstRow2 是我要查找的变量。使用此代码，我得到未过滤的行数。

   CSht.Range(CSht.Cells(1, 1), CSht.Cells(LstRow1, LstCol1)).AutoFilter Field:=2, Criteria1:="RA"
        With CSht
        LstRow2 = .UsedRange.SpecialCells(xlCellTypeLastCell).Row
        End With

Answer 1

您只需要使用可见单元格，因为它已被过滤。

尝试这个:

 With CSht

     'load filter cells into Range object
     Dim rngFilter as Range
     Set rngFilter = Intersect(.UsedRange,.UsedRange.Offset(1)).SpecialCells(xlCellTypeVisible)

     'find the max number of elements split by $ in the range address
       'needs to be dynamic because of areas
     Dim lUpper as Long
     lUpper = UBound(Split(rngFilter.Address,"$"))

     'the last element will be the last row in the filtered range
       'the last number in the rngFilter.Address
     Dim LstRow2 as Long
     LstRow2 = Split(rngFilter.Address,"$")(lUpper)

End With