Clever Geek Handbook

VBA: How to get the current region from the filtered data?

I want to figure out how I can get the data after filtering on a variant object. When I use this:

table = ActiveSheet.Range("A1").CurrentRegion

I am getting all the data, but I only want to filter.

Desktop snapshot: enter image description here

+3


source to share


2 answers


You can use SpecialCells(xlCellTypeVisible)

to get filtered rows:

Dim Tbl As Range

Set Tbl = ActiveSheet.Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible)
' for DEBUG onlu
Debug.Print Tbl.Address


Edit 1 : complete code

Option Explicit

Sub VarfiltRange()

Dim BasketCostFiltRng As Range
Dim LastRow As Long
Dim VarRes As Double

With Worksheets("Sheet1") '< -- modift "Sheet1" to your sheet 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
+4


source


You can use SpecialCells

to get this:



Sheet1.AutoFilter.Range.SpecialCells(xlCellTypeVisible).Address
+2


source






More articles:


All Articles