I need to simply count the number of areas on a sheet. The code I have is :
Sub areas()
Dim i As Long
i = Worksheets("Sheet2").Selection.Areas.Count
MsgBox i
End Sub
But for some reason I get the error message "Object doesn't support this property or method." I have no idea why. This code was basically just copied from the Microsoft website.
I can't even get the immediate window to print the Worksheets("Sheet2").Selection.Areas.Count portion.
Any quick help? I am using Excel 2010.
Thanks.
The answer is
Object doesn't support this property or method.
Think of it like if anything after the dot is called on an object. It's like a chain.
An object is a class instance. A class instance supports some properties defined in that class type definition. It exposes whatever intelli-sense in VBE tells you (there are some hidden members but it's not related to this). So after each dot . you get intelli-sense (that white dropdown) trying to help you pick the correct action.
(you can start either way - front to back or back to front, once you understand how this works you'll be able to identify where the problem occurs)
Type this much anywhere in your code area
Dim a As Worksheets
a.
you get help from VBE, it's a little dropdown called Intelli-sense
It lists all available actions that particular object exposes to any user. You can't see the .Selection member of the Worksheets() class. That's what the error tells you exactly.
Object doesn't support this property or method.
If you look at the example on MSDN
Worksheets("GRA").Activate
iAreaCount = Selection.Areas.Count
It activates the sheet first then calls the Selection... it's not connected together because Selection is not a member of Worksheets() class. Simply, you can't prefix the Selection
What about
Sub DisplayColumnCount()
Dim iAreaCount As Integer
Dim i As Integer
Worksheets("GRA").Activate
iAreaCount = Selection.Areas.Count
If iAreaCount <= 1 Then
MsgBox "The selection contains " & Selection.Columns.Count & " columns."
Else
For i = 1 To iAreaCount
MsgBox "Area " & i & " of the selection contains " & _
Selection.Areas(i).Columns.Count & " columns."
Next i
End If
End Sub
from HERE
Source: Stackoverflow.com