How to get VBA to return a count of selected rows in a filtered range for processing.

[Sej]

Hi,

Forgive me if I use non-programming terms - I am a self taught coder at a very basic level. And I just learn from forums such as this. But this has me stumped.

I have a code where depending on the number of rows selected (in a filtered non-contiguous rows ) - the macro should loop and run that many times.

Here is an extract of the macro:

answer = Selection.Columns(1).SpecialCells(xlCellTypeVisible).Count
For i = 1 To answer
Call FolderName
MsgBox "Should I continue?"
ActiveCell.Offset(1, 0).Activate
Do While ActiveCell.EntireRow.Hidden = True
ActiveCell.Offset(1, 0).Activate
Loop
Next i
End If

This code works perfectly - but only if multiple rows are selected - if only one row is selected - it causes an overflow error.

To bypass that, I have rewritten this to:

answer = MsgBox("Select 'Yes' for 1 and 'No' for more than 1", vbYesNoCancel)
If answer = vbYes Then
Call FolderName
MsgBox "Should I continue?"
End If

If answer = vbNo Then
counter = Selection.Columns(1).SpecialCells(xlCellTypeVisible).Count

For i = 1 To counter
Call FolderName
MsgBox "Should I continue?"
ActiveCell.Offset(1, 0).Activate
Do While ActiveCell.EntireRow.Hidden = True
ActiveCell.Offset(1, 0).Activate
Loop
Next i
End If
If answer = vbCancel Then
Exit Sub
End If

I cannot imagine, I need to jump through such hoops myself to try to make a simple count based loop for filtered non-contiguous rows.

What am I doing wrong? Can this be done better?

 

[Sej]

Good morning Yongle, thank you so much for a detailed explanation. I will mark this as solved.