Iterate over a Range of data - create arrays or ranges
 

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

Iterate over a Range of data - create arrays or ranges
 

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 wrote:

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

Iterate over a Range of data - create arrays or ranges
 

On Jan 29, 7:04 am, Dave Peterson wrote:
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 wrote:

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

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)

Iterate over a Range of data - create arrays or ranges
 

On Jan 29, 7:04*am, Dave Peterson wrote:
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 wrote:

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

Hello Dave,

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

Eric (aka eholz1)