For Each Problem

  • Thread starter Thread starter odie
  • Start date Start date
O

odie

My data is in several areas (contiguous cells) within a column. Blank
(empty) cells separate the areas. I would simply like to loop through each
range but I can't get the For each loop to work:

Data:
A
1 44.6
2 23.9
3 31.8
4
5 50.4
6 24.8
7
8 40.6
9 38.9
10 34.5
11 51.9
12 43.8
13



Dim c as range
For Each c In Worksheets("Master").Range("A1:A" & _
Worksheets("Master").Cells(Rows.Count, "A").End(xlUp).Row)
'do something for each range (area)
Next

What happens is the code loops through every single cell between A1 to the
last used cell in column A. I though it was supposed to loop by range NOT
cell....what gives????

Please help!!, Thanks in advance....

PS: I have tried using code to double check and verify the blank cells are
in fact empty but it did not make a difference.
 
Another way to loop through the range is to limit your range to just the
constants (or even formulas).

If you select A1:A20 and then Edit|goto|Special and click on Constants (then
ok), you'll your range now really is 3 different areas.

Then you could loop cell by cell through that giant range (of discontiguous
areas) or even loop through each area. And you can loop cell by cell through
each area.

Option Explicit
Sub testme()

Dim myRng As Range
Dim myCell As Range
Dim myArea As Range

With Worksheets("sheet1")
Set myRng = Nothing
On Error Resume Next
Set myRng = .Range("a1:a20").Cells.SpecialCells(xlCellTypeConstants)
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "no constants in that range!"
Exit Sub
End If

MsgBox "Doing cell by cell"
For Each myCell In myRng.Cells
MsgBox myCell.Address
Next myCell

'and try this:
MsgBox "Doing Areas!"
For Each myArea In myRng.Areas
MsgBox myArea.Address
For Each myCell In myArea.Cells
MsgBox myCell.Address & "--" & myArea.Count
Next myCell
Next myArea
End With
End Sub
 
Thanks so much to Bernard and Dave for your help. I was able to use/modify
the code you provided to accomplish what I wanted (programmatically adding
XY series of data to a chart by looping through column areas separated by
empty cells. Thanks again...I'm a happy camper once again!!...;)
 
Back
Top