About Us

Jimmy O

Thank you very much Dave, that worked perfectly.

"Dave Peterson" wrote:

If the filled in cells all contain constants (no formulas) and the other cells
in that range (A1:I25) are empty, how about:

Option Explicit
Sub CountAreas()
Dim myRng As Range
Dim myArea As Range
Set myRng = Nothing
On Error Resume Next
Set myRng = ActiveSheet.Range("a1:i25").Cells _
.SpecialCells(xlCellTypeConstants)
On Error GoTo 0

MsgBox myRng.Areas.Count
For Each myArea In myRng.Areas
MsgBox myArea.Address(0, 0)
Next myArea

End Sub

But the areas that excel uses may not be the same as the areas that you would
have used.

Jimmy O wrote:

My initial Sub was

Sub CountAreas()
x = Range("A1:I25").Areas.Count
Range("A30").Value = x
Debug.Print x
End Sub

with the the data already in the three ranges. I added the code to insert
the values into the cells and select stuff because I thought I was doing
something incorrectly. The above code gives x the value of 1 also. I thought
the areas collection count would give the number of non contiguous cells
(containing data) within a given Range.

Thanks everyone for your responses

"Don" wrote:

Dave,

If I'm reading the OP correctly he has a number of ranges defined, there may
or may not be data in a cell or cells in any of these ranges. I think what
he's trying to develop is the number of ranges that do have data in at least
one cell. Might be wrong and hopefully he'll jump in here and clarify a
bit.....

Don

"Dave Peterson" wrote:

I'm not quite sure why you're selecting stuff, but here's another option:

Range("A1:C7,F9:I16,A21:D25").Select
MsgBox Selection.Areas.Count

Jimmy O wrote:

I'm new to VBA and I have a worksheet where I need to determine the number of
contiguous blocks of cells with data in them. There could be two to four
blocks. All of the blocks will be within Range A1:I25. I thought I could use
Areas.Count so I set up a test Sub with three blocks of cells. Here is the
sub:

Sub CountAreas()
Range("A1:C7").Value = 9
Range("F9:I16").Value = 10
Range("A21:D25").Value = 12
Range("A1:I25").Select
x = Selection.Areas.Count
Range("A30").Value = x
Debug.Print x
End Sub

The value of x is 1 when I run the sub. I thought it should be 3. Any help
on what I'm doing incorrectly would be apprciated.

--

Dave Peterson

--

Dave Peterson

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Determining number of columns of 2 different ranges	filo666	Excel Programming	2	January 16th 08 10:09 AM
need help determining a row number	Gary Keramidas	Excel Programming	7	November 13th 05 03:54 AM
Determining if two ranges overlap	Marky[_2_]	Excel Programming	4	February 18th 04 09:54 AM
Determining new category number	Matt[_22_]	Excel Programming	0	November 5th 03 08:53 PM
Determining Row Number	Wes Jester	Excel Programming	2	August 26th 03 09:20 PM

Menu

About Us