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?
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Sej

New Member
Joined
Oct 17, 2020
Messages
17
Platform
  1. Windows
Thanks Dave for responding.

Didn't realise that that is important:

its:
Sub FolderName()
ActiveSheet.Cells(ActiveCell.Row, 16).Select
ActiveCell.Copy
End Sub
 

davesexcel

Well-known Member
Joined
Feb 26, 2006
Messages
1,026
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.
 

Sej

New Member
Joined
Oct 17, 2020
Messages
17
Platform
  1. Windows

ADVERTISEMENT

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
 

Sej

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

Sej

New Member
Joined
Oct 17, 2020
Messages
17
Platform
  1. Windows

ADVERTISEMENT

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
 

davesexcel

Well-known Member
Joined
Feb 26, 2006
Messages
1,026
Possibly you need to count the rows.


answer = Selection.Columns(1).SpecialCells(xlCellTypeVisible).Rows.Count

 

Sej

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

davesexcel

Well-known Member
Joined
Feb 26, 2006
Messages
1,026
Yes, but you don't get an OverFlow error.

Why are you physically selecting filtered data?
 

Watch MrExcel Video

Forum statistics

Threads
1,114,127
Messages
5,546,075
Members
410,726
Latest member
TheSardOz
Top