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

Sej

New Member
Joined
Oct 17, 2020
Messages
17
Platform
  1. Windows
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?
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Thanks Dave for responding.

Didn't realise that that is important:

its:
Sub FolderName()
ActiveSheet.Cells(ActiveCell.Row, 16).Select
ActiveCell.Copy
End Sub
 
Upvote 0
I thought it might help in understanding what you are trying to achieve.

The code does not appear to be doing anything, just looping through column 16 and copying the activecell row.

If you could explain the process to get your desired results.
 
Upvote 0
Gets a little complicated Dave - the code dumps the content of the cell into clipboard and then I actually run an AutoHotkey Script to create folder names in Windows.

I didn't include all that because I felt that, that is immaterial to finding out how to loop based on number of rows selected.

The full code with explanation is here.

answer = MsgBox("Select 'Yes' for 1 and 'No' for more than 1", vbYesNoCancel)
If answer = vbYes Then
Application.Wait (Now + TimeValue("0:00:01") / 50)
Call FolderName '
This copies the name of the cell to be able to be input for the AutoHotKey script file to create new folders in the windows system
Application.Wait (Now + TimeValue("0:00:01") / 50)

Shell ("C:\Program Files\AutoHotkey\AutoHotkey.exe ""C:\Users\Mr Dalal\Documents\AHKScripts\FolderRename.ahk""") '
This creates multiple folders in the windows system

MsgBox "Should I continue?" '(This allows the autohotkey script to complete before going further)

End If

If answer = vbNo Then
answer = Selection.Columns(1).SpecialCells(xlCellTypeVisible).Count
For i = 1 To answer
Application.Wait (Now + TimeValue("0:00:01") / 50)
Call FolderName
Application.Wait (Now + TimeValue("0:00:01") / 50)

Shell ("C:\Program Files\AutoHotkey\AutoHotkey.exe ""C:\Users\Mr Dalal\Documents\AHKScripts\FolderRename.ahk""")

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
 
Upvote 0
Also, the actual macro to which this belongs to is huge, running into more than 150 lines of code doing multiple things, updating cash book, running up monthly revenue data, sending out mails to the internal team using thunderbird, the client a separate mail using thunderbird etc, all of which works - But the code itself isn't the most lovely and efficient code you have seen - its written by a 5 year old, which is me in programming years.

I just want the **** loop to work and I will be set.

Why is there an overflow error if there is only one row selected??

A little more on the business workflow to elaborate.
The rows are essentially, projects. Some clients give multiple projects and some just one. For each project, I use a cloud based service to create folders for my team.
So if a client gives 2 projects, Foldername takes the content of a cell (which is a concatenation of "Project Number, Project Name Client Name, Deadline for the project" and creates 2 folders with that name in the cloud).

What is happening at the moment is that without the answer = MsgBox("Select 'Yes' for 1 and 'No' for more than 1", vbYesNoCancel) everything works when a client gives me more than one project. But if another client pays me for just one project, I get a Overflow error
 
Upvote 0
Sorry forgot to mention a possibly important thing.

If I debug the error, it yellow highlights this line:
answer = Selection.Columns(1).SpecialCells(xlCellTypeVisible).Count
 
Upvote 0
Possibly you need to count the rows.

answer = Selection.Columns(1).SpecialCells(xlCellTypeVisible).Rows.Count
 
Upvote 0
Thank you Dave for your time but tried this code - but in a filtered range of non-contiguous rows - it fails.

If row 45 and row 84 are selected, it only does row 45. It does not move to row 84 So that's a fail

If I select row 45 ONLY, it actually starts processing every row under it too once row 45 is processed!
 
Upvote 0

Forum statistics

Threads
1,212,933
Messages
6,110,751
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top