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?

 

[Yongle]

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

The code will work with filter on. Use this instead

ActiveSheet.AutoFilter.Range.Rows.Count - 1

 

[Yongle]

Sorry - forget that last one
I forgot that you wanted visible rows only

 

[Yongle]

If row 3 is empty

answer = ActiveSheet.Range("A4").CurrentRegion.Resize(, 1).SpecialCells(xlCellTypeVisible).Count - 1

If rows 1 , 2 & 3 contain data adjacent to the table they are included in CurrentRegion

answer = ActiveSheet.Range("A4").CurrentRegion.Resize(, 1).SpecialCells(xlCellTypeVisible).Count - 4

 

[Sej]

Yongle, there may be unselected rows in the filtered rows.....

eg: If the filter has 35 rows, my selected rows may just be row number 4 and row number 6.

Wont your code take ALL the 35 rows visible?

 

[Yongle]

That was why I asked this questionin post#18 ... do you want to count every visible row except the header ?

Are you selecting manually?
- perhaps VBA could be used to filter the data to match your manual selection
- how are you choosing the rows that you are selecting?

OR re you selecting via VBA?
- please post the code

 

[Sej]

Apologies, I missed that - no it is never ALL visible rows.

- I am selecting manually, but the business workflow is so complicated that VBA cannot match the selection.
- I am selecting filtered non-contiguous rows.

The macro HAS to run on selected rows.

 

[Yongle]

Thanks
I'm busy until tomorrow morning. I will update the thread then.

but the business workflow is so complicated that VBA cannot match the selection.

- wait and see

 

[Sej]

Thank you Yongle.

 

[Yongle]

Explanation of the approach
- use VBA to colourfill cell in designated column in each selected row
- use that column as an additional filter

Simple Example

1. Copy the data below into Sheet1 in a new workbook

Book1
	A	B	C	D
1
2
3
4	Name	Tag	Ref	Date
5	Name01	A	R0050	07/10/2020
6	Name02	B	R0064	01/10/2020
7	Name03	A	R0077	01/10/2020
8	Name04	A	R0086	08/10/2020
9	Name05	A	R0088	02/10/2020
10	Name06	B	R0027	05/10/2020
11	Name07	B	R0088	29/09/2020
12	Name08	A	R0096	05/10/2020
13	Name09	A	R0060	28/09/2020
14	Name10	B	R0085	07/10/2020
Raw Data

2. Select cell A4 and click on Data \ Filter

3. Insert the code below in Sheet1 code window (NOT in a module)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    With Target
        If .Row < 5 Or .Column <> 1 Then Exit Sub
        With .Interior
            If .ColorIndex = 43 Then .ColorIndex = -4142 Else .ColorIndex = 43
        End With
    End With
End Sub

4. In column Tag manually filter for Tag A

Book1
	A	B	C	D
1
2
3
4	Name	Tag	Ref	Date
5	Name01	A	R0050	07/10/2020
7	Name03	A	R0077	01/10/2020
8	Name04	A	R0086	08/10/2020
9	Name05	A	R0088	02/10/2020
12	Name08	A	R0096	05/10/2020
13	Name09	A	R0060	28/09/2020
Raw Data

5. Click on a few cells in column A - cells turn green when clicked (cancelled if clicked again)

Book1
	A	B	C	D
1
2
3
4	Name	Tag	Ref	Date
5	Name01	A	R0050	07/10/2020
7	Name03	A	R0077	01/10/2020
8	Name04	A	R0086	08/10/2020
9	Name05	A	R0088	02/10/2020
12	Name08	A	R0096	05/10/2020
13	Name09	A	R0060	28/09/2020
Raw Data

6. Click on dropdown in column A and filter by colour

Book1
	A	B	C	D
1
2
3
4	Name	Tag	Ref	Date
5	Name01	A	R0050	07/10/2020
9	Name05	A	R0088	02/10/2020
12	Name08	A	R0096	05/10/2020
13	Name09	A	R0060	28/09/2020
Raw Data

7. Next steps
- think about what we need to do to make this work for you
- which column do you want to click to make the selection?
- should a different column be used to for the colour?
- etc
- I have kept it VERY simple but we can make it more sophisticated to match your requirements
- the important target is that we need the data FILTERED and then VBA is not relying on selected cells which is what is causing your difficulties

Let me know if you require any help to develop it further

 

[Sej]

Explanation of the approach
- use VBA to colourfill cell in designated column in each selected row
- use that column as an additional filter

Simple Example

1. Copy the data below into Sheet1 in a new workbook

Book1
	A	B	C	D
1
2
3
4	Name	Tag	Ref	Date
5	Name01	A	R0050	07/10/2020
6	Name02	B	R0064	01/10/2020
7	Name03	A	R0077	01/10/2020
8	Name04	A	R0086	08/10/2020
9	Name05	A	R0088	02/10/2020
10	Name06	B	R0027	05/10/2020
11	Name07	B	R0088	29/09/2020
12	Name08	A	R0096	05/10/2020
13	Name09	A	R0060	28/09/2020
14	Name10	B	R0085	07/10/2020
Raw Data

2. Select cell A4 and click on Data \ Filter

3. Insert the code below in Sheet1 code window (NOT in a module)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    With Target
        If .Row < 5 Or .Column <> 1 Then Exit Sub
        With .Interior
            If .ColorIndex = 43 Then .ColorIndex = -4142 Else .ColorIndex = 43
        End With
    End With
End Sub

4. In column Tag manually filter for Tag A

Book1
	A	B	C	D
1
2
3
4	Name	Tag	Ref	Date
5	Name01	A	R0050	07/10/2020
7	Name03	A	R0077	01/10/2020
8	Name04	A	R0086	08/10/2020
9	Name05	A	R0088	02/10/2020
12	Name08	A	R0096	05/10/2020
13	Name09	A	R0060	28/09/2020
Raw Data

5. Click on a few cells in column A - cells turn green when clicked (cancelled if clicked again)

Book1
	A	B	C	D
1
2
3
4	Name	Tag	Ref	Date
5	Name01	A	R0050	07/10/2020
7	Name03	A	R0077	01/10/2020
8	Name04	A	R0086	08/10/2020
9	Name05	A	R0088	02/10/2020
12	Name08	A	R0096	05/10/2020
13	Name09	A	R0060	28/09/2020
Raw Data

6. Click on dropdown in column A and filter by colour

Book1
	A	B	C	D
1
2
3
4	Name	Tag	Ref	Date
5	Name01	A	R0050	07/10/2020
9	Name05	A	R0088	02/10/2020
12	Name08	A	R0096	05/10/2020
13	Name09	A	R0060	28/09/2020
Raw Data

7. Next steps
- think about what we need to do to make this work for you
- which column do you want to click to make the selection?
- should a different column be used to for the colour?
- etc
- I have kept it VERY simple but we can make it more sophisticated to match your requirements
- the important target is that we need the data FILTERED and then VBA is not relying on selected cells which is what is causing your difficulties

Let me know if you require any help to develop it further

Wow, ok let me figure this out at work on Monday and will post my findings. Thank you Yongle.