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)