SpecialCells(xlCellTypeVisible)
来获取筛选后的行:Dim Tbl As Range
Set Tbl = ActiveSheet.Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible)
' for DEBUG onlu
Debug.Print Tbl.Address
编辑 1: 完整代码
Option Explicit
Sub VarfiltRange()
Dim BasketCostFiltRng As Range
Dim LastRow As Long
Dim VarRes As Double
With Worksheets("Sheet1") '< -- modift "Sheet1" to your sheet's name
LastRow = .Cells(.Rows.Count, "D").End(xlUp).Row
' get only the filteres rows in column D
Set BasketCostFiltRng = .Range("D2:D" & LastRow).SpecialCells(xlCellTypeVisible)
' get the variance of only visible cells in Column "D" (after you filter to show only 1100 and 1112 in column "A")
VarRes = WorksheetFunction.Var(BasketCostFiltRng)
MsgBox VarRes
End With
End Sub
SpecialCells
来获取它:Sheet1.AutoFilter.Range.SpecialCells(xlCellTypeVisible).Address