CODEWITHSUNDEEP
excelvbaloops
Lugarini
Lugarini

Reputation: 802

How to loop Excel cells Filter

I have a column that I want to Filter values:

enter image description here

Now I want to loop only on filtered cells
This is what i have done:

For lin = 2 to lastLine    
    If Not Plan11.Cells(lin, 1).Hidden Then
          //do something
    End If
Next

But I have more than 50000 cells... is there a faster way to do this?

Upvotes: 1

Views: 162

Answers (2)

Reza Aghaei
Reza Aghaei

Reputation: 125197

You can find filtered cells using:

ActiveSheet.range("valid range for column").Rows.SpecialCells(xlCellTypeVisible)

Then you can iterate on result and do stuff.

For example, When I have a table I can do it this way:

Dim lastRow
lastRow = ActiveSheet.ListObjects("Table1").range.Rows.Count
Dim range
Set range = ActiveSheet.range("A2:A" & lastRow).Rows.SpecialCells(xlCellTypeVisible)
For Each rCell In range.Cells
    MsgBox rCell.Value
Next rCell

Upvotes: 1

user3598756
user3598756

Reputation: 29421

use SpecialCells method of the range object

with ws.Columns(1).SpecialCells(xlCellTypeVisible)
'... do something.
'    for instance:  .Font.Bold = True
end with

where ws is the worksheet object whose column "A" you want to work with

Upvotes: 1

Related Questions