How to move to the next filtered row/col in VBA?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

Is there a relatively simple piece of code which will allow me to move down to the 'adjacent' row/column

I wish to do this because I have a filtered list, and want to move to the 'next' filtered row, although this may not, in actual fact, be the next row, so the offset property would not help here

Eg. Filtered Rows 4 and 10 contain my criteria, so normally if I press the down arrow, it'll jump from row 4 to row 10. How can I achieve this using VBA
 
Hello,

You can use SpecialCells(xlCellTypeVisible).


Code:
--------------------

Dim rng As Range, c As Range
Set rng = ActiveSheet.AutoFilter.Range.SpecialCells(xlCellTypeVisible)
For Each c In rng.Rows
MsgBox c.Row
c.Select
Next

--------------------
 
Hi Colo

Thanks for your tip. Would there be a way of not selecting the 1st row of the filter

For example

Row 1 contains my column headings, and row 2 onwards is the actual data. The filter dropdown button is located in row 1. Now, if row 4 and 10 has been filtered out, can I then select the data in row 4 and 10, but not the headings in row 1? Currently the code below selects row 1 in addition to 4 and 10

Thanks very much in advance

Code
-------------------

Dim rng As Range, c As Rang
Set rng = ActiveSheet.AutoFilter.Range.SpecialCells(xlCellTypeVisible
For Each c In rng.Row
MsgBox c.Ro
c.Selec
Nex

--------------------
 
Hi ~~,

Yes it can be done with offset and resize propertis! ;)


Code
-------------------

Dim rng As Range, c As Range
With ActiveSheet.AutoFilter.Range
Set rng = .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
End With
For Each c In rng.Rows
MsgBox c.Row
c.Select
Next
 
Back
Top