Dynamically Select Number of Filtered Rows In Current Column in Excel

Question

Is there a way (maybe a VBA) in Excel to select a set number of filtered rows based upon the current column? Current work flows regularly require us to select 25 or 100 filtered rows of the active column at a time.

Most macros I've come across during research appear to

Select all data in a table or column
Involve a pre-defined column.

I initially thought this would be a strait-forward macro to develop but have had difficulty. The code I found during research moves down a set number of rows but does not appear to factor in filtered rows when counting.

Sub MoveOneCellDownAFilteredList()
    ActiveCell.Select
    Range(Selection, Selection.Offset(2, 0)).Select
    Do Until ActiveCell.EntireRow.Hidden = False
        ActiveCell.Offset(2, 0).Select
    Loop
End Sub

Any help is appreciated.

What do you need to do with them after they're selected? Copy? — Tim Williams, Feb 28 '18 at 06:58

score 1 · Answer 1 · answered Feb 28 '18 at 07:41

The following are just examples (no ready to use copy-paste-solution), but they should give you the right hint to solve your issue:

To select only visible cells of a specific range you can use the Range.SpecialCells method.

The following selects only the visible cells out of range "A1:A200"
```
Range("A1:A200").SpecialCells(xlCellTypeVisible).Select
```
To e.g. select all visible entire rows of the active column you could use something like
```
ActiveCell.EntireColumn.SpecialCells(xlCellTypeVisible).EntireRow.Select
```

or to select only rows that are visible and also have a constant value you can combine them like

ActiveCell.EntireColumn.SpecialCells(xlCellTypeConstants).SpecialCells(xlCellTypeVisible).EntireRow.Select

Read the documentation for further options of SpecialCells.

Note that I recommend not to use .Select at all if you want to do any further actions beside selecting. I recommend to read How to avoid using Select in Excel VBA for a good practice and better, faster more reliable code.

Dynamically Select Number of Filtered Rows In Current Column in Excel

1 Answers1