Link to home
Start Free TrialLog in
Avatar of Cory Vandenberg
Cory VandenbergFlag for United States of America

asked on

Infinite loop when Range is only 1 cell

Just curious if anyone knows why this code would enter an infinite loop.  When there is more than one value visible in the column it works just fine.  I've added a simple conditional for now, but just curious to know if there is another way around it.


For Each cel In .Range("A2:A" & .Range("A" & .Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeVisible)
                cboUnit.AddItem cel.Offset(0, 1)
            Next cel

Open in new window

Avatar of syeager305
syeager305

can you provide the complete code?... :)
Avatar of Cory Vandenberg

ASKER

I can do you one better.  Here is the solution I posted for another question.

https://www.experts-exchange.com/questions/24987910/Populating-Drop-Down-with-filtered-records.html

Download the workbook I put there.  It's in the userform code that populates the comboboxes.  You'll need to take out the conditional statement that takes care of the case when there is only 1 cell to see what I'm talking about.

Cheers,
WC
Oh ya, and when you load the form, select the first choice for the first dropdown, Group.  This only has 1 Unit, which will cause the issue.
ASKER CERTIFIED SOLUTION
Avatar of zorvek (Kevin Jones)
zorvek (Kevin Jones)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Kevin,

Thank you very much for the detailed answer and alternative solutions to avoid this problem.  I am definitely going to look into this some over my vacation the next couple of weeks.  It's the first time I've run into this, as I have never really needed to use the SpecialCells functionality before.  It's something I won't be forgetting though.  

Thanks again,
WC
Very nice answer, and two options for getting around this.  Love it.