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]

No I don't get an overflow error. But I need the filter because the sheet has completed projects, ongoing projects and prospective projects - the filter takes out the former two to allow me to run the macro only on prospective projects.

The data may be non-contiguous because sometimes I need the filter to sort with deadlines too, so I know whether for a client I may be able to do Project X (row 45) but not Project Y (row 64) because after the filter it is still sorted by deadline.

Further I need to physically select rows, because a client may pay for Project X and Project Z but not Project Y.

 

[Sej]

My apologies, but my business workflows aren't the problem here. I am unlikely to change them to get a macro to work

Basic question remains:
answer = Selection.Columns(1).SpecialCells(xlCellTypeVisible).Count
does not work when rows are filtered and non-contiguous and I select 1 row. For more than 1 row it works.

What am I doing wrong

 

[Yongle]

VBA cannot handle what you are doing
- run this macro and you will see

Sub WhatIsGoingOn()
    With Union([a1], [b1], [a2], [b2]).Cells
        MsgBox .Address(0, 0) & vbCr & "Cells " & .Cells.Count & vbCr & "Columns " & .Columns.Count & vbCr & "Rows " & .Rows.Count, , "Contiguous"
    End With
    With Union([a1], [b1], [a2], [b7]).Cells
        MsgBox .Address(0, 0) & vbCr & "Cells " & .Cells.Count & vbCr & "Columns " & .Columns.Count & vbCr & "Rows " & .Rows.Count, , "Non-Contiguous"
    End With
    With Union([a1], [a3], [a4], [a10]).Cells
        MsgBox .Address(0, 0) & vbCr & "Cells " & .Cells.Count & vbCr & "Columns " & .Columns.Count & vbCr & "Rows " & .Rows.Count, , "Non-Contiguous"
    End With
End Sub

 

[Sej]

Wow - never would have thought it would be this complex.

Thanks Dave and Yongle. Will stick to using my current solution of using VBYesNO for choosing between 1 project and multiple projects.

 

[davesexcel]

Yes I get the error as well, that's why I suggested doing a rows.count to compensate for the error.
I was asking questions seeing if I could help you with your code.

I think that if a user just selects one cell, chances are that cell will be visible so you would not have to check if it is .
So if you could count the selected cells first.

if selection.count=1 then
'do something
else
'do something else
end if

 

[Sej]

Yes I get the error as well, that's why I suggested doing a rows.count to compensate for the error.
I was asking questions seeing if I could help you with your code.

I think that if a user just selects one cell, chances are that cell will be visible so you would not have to check if it is .
So if you could count the selected cells first.

if selection.count=1 then
'do something
else
'do something else
end if

Ah a ray of hope. Thank you Dave for taking the time to understand my business workflow to solve this. Your code seems to be a simple solution. So where do I put this bit of code? Like I said, I am self taught - and just do coding to help my business.

However, there will never be just one cell selected. One row yes before running this, but one cell never.

Dave, would you be able to place your code in mine and paste it for me please?

 

[Yongle]

The problem in your original code is caused by using that syntax when the range is filtered

- filter the data and run the code below
- clear filter so that every row is visible and run the code

The error only happens when there is at least one non-visible row

Sub CountItems()
    Dim answer As Long
    On Error Resume Next
    answer = Selection.Columns(1).SpecialCells(xlCellTypeVisible).Count
    MsgBox answer & vbCr & Err.Description
End Sub

 

[Yongle]

@Sej
Your approach is unsafe
A workaround may solve an immediate issue but (whenever possible) it would be better to a adopt a method where the correct results could be guaranteed
(not just here - but always!)

Is the first header in cell A1 ? do you want to count every visible row except the header ?

    answer = ActiveSheet.Range("A1").CurrentRegion.Resize(, 1).SpecialCells(xlCellTypeVisible).Count - 1
    If answer > 0 Then
        'do something
    End If

 

[Sej]

The problem in your original code is caused by using that syntax when the range is filtered

- filter the data and run the code below
- clear filter so that every row is visible and run the code

The error only happens when there is at least one non-visible row

Sub CountItems()
    Dim answer As Long
    On Error Resume Next
    answer = Selection.Columns(1).SpecialCells(xlCellTypeVisible).Count
    MsgBox answer & vbCr & Err.Description
End Sub

Thanks Yongle, but removing the filter is not possible because there are hundreds of rows.

 

[Sej]

@Sej
Your approach is unsafe
A workaround may solve an immediate issue but (whenever possible) it would be better to a adopt a method where the correct results could be guaranteed
(not just here - but always!)

Is the first header in cell A1 ? do you want to count every visible row except the header ?

    answer = ActiveSheet.Range("A1").CurrentRegion.Resize(, 1).SpecialCells(xlCellTypeVisible).Count - 1
    If answer > 0 Then
        'do something
    End If

The header is actually in A4 - but will this code work with a filter on?