Iterate over a Range of data - create arrays or ranges

[eholz1]

Hello Excel Programmers,
I am using VBA to create and read ranges on worksheet.

I have a question on how to read and save data in a given range.
I would like to use some sort of Range() method to check the data.

I my case an empty row or two indicates that a new range (or an array)
needs to be created. I would like to read down a given column, if
there are 1 or 2 blank rows, then a new range or data in an array
should begin.

I can use a FOR loop and start at the top of the range, and work down,
doing an "IF" statement when I encounter a blank row or two, define a
new range until next blank row, and write data into a new array each
time a 1 or 2 blank rows are found.

I am wondering if there is a "nicer" way to do this with a range
object and a collection?

I have the start of the range, and the end of the range
(LastUsedRow). I will end up with 5 or 6 arrays (or ranges), so I can
access the data in each of the 6 arrays or ranges). The data are
worksheet names for which I need to do some calculations on. Like add
the numbers on the following sheets, etc. the sheets are all in the
same workbook.

thanks for the info available on this forum,

eholz1

 

[Dave Peterson]

Are these all constants in your range?

If they are all constants...

Dim myRng as range
dim myArea as range
dim myCell as range

with worksheets("Sheet1")
set myrng = nothing
on error resume next
set myrng = .range("a1",.cells(.rows.count,"A").end(xlup)) _
.cells.specialcells(xlcelltypeconstants)
on error goto 0
end with

if myrng is nothing then
msgbox "no constants in column A!"
exit sub
end if

for each myarea in myrng.areas
msgbox myarea.address 'or whatever you want to do
for each mycell in myarea.cells
msgbox mycell.address
next mycell
next myarea

(Untested, uncompiled. Watch for typos.)

In fact, if your data were all formulas, you could do the same kind of thing.
Just change the .specialcells() type.

If you have a mixture of formulas and constants, it gets just a bit more
complex.

 

[eholz1]

Are these all constants in your range?

If they are all constants...

Dim myRng as range
dim myArea as range
dim myCell as range

with worksheets("Sheet1")
set myrng = nothing
on error resume next
set myrng = .range("a1",.cells(.rows.count,"A").end(xlup)) _
.cells.specialcells(xlcelltypeconstants)
on error goto 0
end with

if myrng is nothing then
msgbox "no constants in column A!"
exit sub
end if

for each myarea in myrng.areas
msgbox myarea.address 'or whatever you want to do
for each mycell in myarea.cells
msgbox mycell.address
next mycell
next myarea

(Untested, uncompiled. Watch for typos.)

In fact, if your data were all formulas, you could do the same kind of thing.
Just change the .specialcells() type.

If you have a mixture of formulas and constants, it gets just a bit more
complex.

Hello Dave (and future respondents),

Thank you for the sample code and guidance. I will put this together,
and see what happens.
I will get back to the forum with the results.

Thanks again,

eric (aka eholz1)

 

[eholz1]

Are these all constants in your range?

If they are all constants...

Dim myRng as range
dim myArea as range
dim myCell as range

with worksheets("Sheet1")
  set myrng = nothing
  on error resume next
  set myrng = .range("a1",.cells(.rows.count,"A").end(xlup)) _
                 .cells.specialcells(xlcelltypeconstants)
  on error goto 0
end with

if myrng is nothing then
  msgbox "no constants in column A!"
  exit sub
end if

for each myarea in myrng.areas
   msgbox myarea.address 'or whatever you want to do
   for each mycell in myarea.cells
      msgbox mycell.address
   next mycell
next myarea

(Untested, uncompiled.  Watch for typos.)

In fact, if your data were all formulas, you could do the same kind of thing.
Just change the .specialcells() type.

If you have a mixture of formulas and constants, it gets just a bit more
complex.

Hello Dave,

Your code suggestion and sample worked perfectly!!! Thanks,

Eric (aka eholz1)