CODEWITHSUNDEEP
vbaexcel
sharkantipav
sharkantipav

Reputation: 139

Selected a Filtered range

I have a list Object that I filter by date. Once I have a filtered array. I would like to set a range on Column D to have a For loop that read the filtered range

Here is my code

ActiveSheet.ListObjects("Invoices").Range.AutoFilter Field:=5, Criteria1:="=" & Year.value
ActiveSheet.ListObjects("Invoices").Range.AutoFilter Field:=6, Criteria1:="=" & Month.value

Dim r As Range
Set r = Sheets("Invoices").Range(Range("D1"), Range("D1").End(xlDown)).Offset(1, 0)
MsgBox r.Address

Column D have a Header in D1. When I filter my range should be D75:D90 ... but the msgbox returns D2:D90 like if the Data were not filtered. Thx

Upvotes: 0

Views: 96

Answers (2)

L42
L42

Reputation: 19727

You're almost there. Just use SpecialCells property like below:

Dim r As Range, c As Range

With Sheets("Invoices").Range(Range("D1"), Range("D1").End(xlDown))
    Set r = .Offset(1, 0).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
End With
'~~> Use Areas property if you want to loop
'~~> through the filtered data as posted by lukas2
For Each c In r.Areas
    Msgbox c.Address
Next

Also you need to resize your range to explicitly work on cells with values.
That is to compensate your offset to exclude the headers.

Upvotes: 1

Lukas2
Lukas2

Reputation: 320

to check filtered items you have to loop over areas, eg:

Dim Filtered As Range
Dim a As Variant
Dim g As Variant

Set Filtered = Selection.SpecialCells(xlCellTypeVisible)

For Each a In Filtered.Areas
    For Each g In a.Rows
        MsgBox g.Address
    Next g
Next a

Upvotes: 0

Related Questions