Jimmy,

Still don't know if I understand correctly what you're trying to do, but if
I do, this code with some revisions to suit your set up will give you the
number of Ranges that have at least one cell with data in it.....

Option Explicit

Sub NumOfRges()

Dim Rge1 As Range
Dim Rge2 As Range
Dim Cell As Range
Dim x1, x2 As Integer

x1 = 0
x2 = 0
Set Rge1 = Range("A1:D7")
Set Rge2 = Range("C17:D22")

For Each Cell In Rge1
If Cell.Value < "" Then
x1 = 1
End If
Next

For Each Cell In Rge2
If Cell.Value < "" Then
x2 = 1
End If
Next

MsgBox ("x1 = ") & x1
MsgBox ("x2 = ") & x2
MsgBox ("Total Ranges with Data = ") & x1 + x2

End Sub


Probably a cleaner way to get there but this does work. You will have to
define each range within the macro and loop through each rge.

HTH,

Don

"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