VBA, Excel - iterating through cells selected in filtered column

Question

In a worksheet with data there is a column with an applied filter to limit the displayed data. The user selects 1 or more cells (not necessarily continuous) in a column and execute a VBA code. In the VBA code I'd like to iterate through selected cells and do some operations with them, but there is difference in Excel behavior when only 1 cell is selected (active in Excel terms). Code that works:

Sub Macro1()
    If Selection.count = 1 Then
        counter = 1
        Debug.Print Selection.Text
    Else
        counter = Selection.SpecialCells(xlCellTypeVisible).count
        For Each c In Selection.SpecialCells(xlCellTypeVisible)
             Debug.Print c.Text
        Next c
    End If
    Debug.Print counter
End Sub

QUESTION: Is there a way, more elegant and clean solution to do this? To get rid of If-Then?

Selection.SpecialCells(xlCellTypeVisible).count

generates overflow error if only one cell is activated (I think Excel expands selection to whole worksheet)

ActiveCell.Select
Selection.SpecialCells(xlCellTypeVisible).count

returns 2 if only one cell is selected (returns selected record twice)

EDIT Please note: filter is applied manually by the user not by the VBA code. Also user manually selects cells from filtered view and that selected cells are next used in VBA code.

So.... you're stepping one row down off the visible header and resizing the range to process with .Rows.Count-1 ...? Then looking at a single column ...? Try to avoid Selection as a means of determining the range to be processed. In any event, it would be Selection.SpecialCells(xlCellTypeVisible).Count not Selection.Count. — user4039065
No, user selects from 1 to many cells in filtered table. That selection is what macro has to work with. Header is not important here. As i wrote Selection.SpecialCells(xlCellTypeVisible).Count don't work if user selected only one record. — b0rek

Unknown Unknown · Accepted Answer · 2015-12-03T10:58:13

The following is based on this sample data.

     Column A   Column A   Column C
      a           b           c
1    AA-01       BB-01        1
2    AAA-02      BBB-02       2
3    AAAA-03     BBBB-03      2

These are the methods I use for the AutoFilter Method. I do not have any trouble dealing with one or more than one visible row and have no need to distinguish between the filter sets.

Sub filter_test()
    With Worksheets("Sheet16")  '<~~set this properly
        If .AutoFilterMode Then .AutoFilterMode = False
        With .Cells(1, 1).CurrentRegion
            .AutoFilter field:=3, Criteria1:=1
            'report on column A
            With .Resize(.Rows.Count - 1, 1).Offset(1, 0)
                If CBool(Application.Subtotal(103, .Cells)) Then
                    reportVisibleCells visRng:=.Cells
                Else
                    Debug.Print "no visible cells with 1"
                End If
            End With
            .AutoFilter field:=3
            .AutoFilter field:=3, Criteria1:=2
            'report on column B
            With .Resize(.Rows.Count - 1, 1).Offset(1, 1)
                If CBool(Application.Subtotal(103, .Cells)) Then
                    reportVisibleCells visRng:=.Cells
                Else
                    Debug.Print "no visible cells with 2"
                End If
            End With
            .AutoFilter field:=3
            .AutoFilter field:=3, Criteria1:=3
            'report on column C
            With .Resize(.Rows.Count - 1, 1).Offset(1, 2)
                If CBool(Application.Subtotal(103, .Cells)) Then
                    reportVisibleCells visRng:=.Cells
                Else
                    Debug.Print "no visible cells with 3"
                End If
            End With
            .AutoFilter field:=3
        End With
        If .AutoFilterMode Then .AutoFilterMode = False
    End With
End Sub

Sub reportVisibleCells(visRng As Range)
    Dim vr As Range

    With visRng.SpecialCells(xlCellTypeVisible)
        For Each vr In .Cells
            Debug.Print vr.Text
        Next vr
        Debug.Print .Count
    End With
End Sub

Set up your desktop so you can see both the worksheet and the VBE window. Open the VBE's Immediate window (Ctrl+G) so you can see the Debug.Print reporting. Put the cursor in the filter_test sub and start tapping F8 to walk through.

Expected results from the VBE's Immediate window.

AA-01
 1 
BBB-02
BBBB-03
 2 
no visible cells with 3

VBA, Excel - iterating through cells selected in filtered column

2 Answers