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?
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?