[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Infinite loop when Range is only 1 cell

Posted on 2009-12-17
6
Medium Priority
?
322 Views
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

0
Comment
Question by:Cory Vandenberg
  • 4
6 Comments
 
LVL 5

Expert Comment

by:syeager305
ID: 26077045
can you provide the complete code?... :)
0
 
LVL 18

Author Comment

by:Cory Vandenberg
ID: 26077086
I can do you one better.  Here is the solution I posted for another question.

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_24987910.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
0
 
LVL 18

Author Comment

by:Cory Vandenberg
ID: 26077091
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.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 2000 total points
ID: 26077547
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)
      Else
         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
         Else
            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]

Kevin
0
 
LVL 18

Author Comment

by:Cory Vandenberg
ID: 26078469
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
0
 
LVL 18

Author Closing Comment

by:Cory Vandenberg
ID: 31667603
Very nice answer, and two options for getting around this.  Love it.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

830 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