Question concerning Areas Property

[Mindpsyche]

Hello everyone,

In my self learning of vba I stumbled upon a neat website with some nice VBA beginer tips: so i found this website via a linkedin group.

This is the website: http://www.excel-spreadsheet.com/vba/vba.htm

So in one of the sections I found a code that explains the Areas property.

However, when i copy paste this code to the vba editor it gives an error.

Something with the range selection:

This is the code:

Sub AreaExample()
    Sheets(1).Select
    UsedRange.Select
    MsgBox Selection.Area.Count 'Number '3' - ranges returned
End Sub

Can someone please explain whats wrong with this?

Thanks

 

[mikerickson]

I think you are missing an "s".

Sub AreaExample()
    Sheets(1).Select
    UsedRange.Select
    MsgBox Selection.Area[COLOR="Red"]s[/COLOR].Count 'Number '3' - ranges returned
End Sub

 

[Mindpsyche]

I think you are missing an "s".

Sub AreaExample()
    Sheets(1).Select
    UsedRange.Select
    MsgBox Selection.Area[COLOR="Red"]s[/COLOR].Count 'Number '3' - ranges returned
End Sub

Hi Mike,

Thanks for your help.

It still returns 1 to me instead of 3.

This is how my sheet looks:

Excel 2007
	A	B	C	D	E	F	G	H
1	12
2	12
3
4
5
6				12
7				12
8
9
10
11
12								12
13								12
Sheet1

So I have 3 non-contigous ranges for which the areas function should give me 3 right? Instead I only get one as the answer.

Here is my adjusted code:

Sub AreaExample()
    Sheets(1).Select
    ActiveSheet.UsedRange.Select
    MsgBox Selection.Areas.Count 'Number '3' - ranges returned
End Sub

I'm trying to understand the functionality of the areas function. As i have read it should return the number of non-contigous ranges within a selection.

By the way the actualy code from the website was:

Sub AreaExample() 
    Range("A1:B2", E4, G10:J25").Select 
    MsgBox Selection.Area.Count 'Number '3' - ranges returned
End Sub

Which also gives me an error...at the Range("A1:B2", E4, G10:J25").Select step.

 

[mikerickson]

Try this

With Sheets(1)
   
    With .UsedRange
        MsgBox "Used Range: " & .Address & vbCr & .Areas.Count
    End With
    
    With .Cells.SpecialCells(xlCellTypeConstants)
        MsgBox "Cells with entries: " & .Address & vbCr & .Areas.Count
    End With

End With

P.S. the error in the code from the website is the " after B2

 

[Mindpsyche]

Try this

With Sheets(1)
   
    With .UsedRange
        MsgBox "Used Range: " & .Address & vbCr & .Areas.Count
    End With
    
    With .Cells.SpecialCells(xlCellTypeConstants)
        MsgBox "Cells with entries: " & .Address & vbCr & .Areas.Count
    End With

End With

P.S. the error in the code from the website is the " after B2

Thanks a lot Mike!