Infinite loop when Range is only 1 cell

Posted on 2009-12-17
Last Modified: 2012-05-08
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

Question by:WarCrimes
    LVL 5

    Expert Comment

    can you provide the complete code?... :)
    LVL 18

    Author Comment

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

    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.

    LVL 18

    Author Comment

    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.
    LVL 81

    Accepted Solution

    The problem is that SpecialCells has a bug in that it returns all the cells on the worksheet when used on only one cell. There are two basic solutions to work around the bug. You can use a replacement to SpecialCells (see below) or you can avoid using it:

       For Each cel In .Range("A2:A" & .Range("A" & .Rows.Count).End(xlUp).Row)
          If Not cel.EntireRow.Hidden And Not cel.EntireColumn.Hidden Then cboUnit.AddItem cel.Offset(0, 1)
       Next cel

    The function below implements the same functionality as the SpecialCells method with three differences: it operates as expected on single cells (uses the single cell versus the entire used range of the parent worksheet), it allows specification of multiple cell types, and it does not require that error handling be turned off. See the comments in the routine for more information about the parameters and usage.

    [Begin Code Segment]

    Public Function SpecialCells( _
          ByVal SourceRange As Range, _
          ByVal SpecialCellsType As Variant, _
          ByVal SpecialCellsValue As XlSpecialCellsValue _
       ) As Range

    ' Function returns the same result as the SpecialCells method with two
    ' differences: it allows multiple cell types to be specified and it operates
    ' correctly in single cells.
    ' Syntax
    ' SpecialCells(SourceRange, SpecialCellsType, SpecialCellsValue)
    ' SourceRange - A range object.
    ' SpecialCellsType - A single cell type or multiple cell types as an array. Use
    '   the same constants as passed to the SpecialCells method.
    ' SpecialCellsValue - Same as the Value parameter to the SpecialCells method.
    ' Example - Find all numeric cells containing formulas or constants:
    '   Set AllNumericCells = SpecialCells(UsedRange, Array(xlCellTypeConstants, xlCellTypeFormulas), xlNumbers)

       Dim SingleCellSource As Range
       Dim CellType As Variant
       Dim SpecialCellsRange As Range
       Dim ResultRange As Range
       If SourceRange.Cells.Count = 1 Then
          Set SingleCellSource = SourceRange
          If SourceRange.Row < SourceRange.Parent.Rows.Count Then
             Set SourceRange = SourceRange.Resize(2)
             Set SourceRange = SourceRange.Offset(-1).Resize(2)
          End If
       End If
       If Not IsArray(SpecialCellsType) Then SpecialCellsType = Array(SpecialCellsType)
       For Each CellType In SpecialCellsType
          Set SpecialCellsRange = Nothing
          On Error Resume Next
          Set SpecialCellsRange = SourceRange.SpecialCells(CellType, xlNumbers)
          On Error GoTo 0
          If Not SpecialCellsRange Is Nothing Then
             If ResultRange Is Nothing Then
                Set ResultRange = SpecialCellsRange
                Set ResultRange = Union(ResultRange, SpecialCellsRange)
             End If
          End If
       Next CellType
       If Not SingleCellSource Is Nothing And Not ResultRange Is Nothing Then
          Set ResultRange = Intersect(ResultRange, SingleCellSource)
       End If
       Set SpecialCells = ResultRange

    End Function

    [End Code Segment]

    LVL 18

    Author Comment


    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,
    LVL 18

    Author Closing Comment

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Suggested Solutions

    Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
    This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
    The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
    This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now