Areas Collection Question

[Takeadoe]

Dear NG members,

I've embedded my questions in the code below.

Sub GraphByUniqueCategory()
Dim myList() As Variant
Dim i As Integer
Dim j As Integer
Dim myCount As Integer
Dim chtDeer As Chart
Dim shtData As Worksheet
Dim rngData As Range
Dim myDataSet As Range
Dim strCounty As String

myCount = 1

Set shtData = Worksheets("Sheet1")


HERE IS WHAT I'VE READ FROM OTHER POSTS REGARDING THE AREAS COLLECTION:

1) The Range object has an Areas collection that allows you to access
multiple noncontiguous ranges in a selection. Therefore, if
Selection.Areas.Count=1, then you know that the selected range is
contiguous. If Selection.Areas.Count>1 then you can access the
different parts of the selection with "Dim rngArea As Range"

2) Each range in the Areas collection is a rectangular range of
contiguous cells.

FROM THE DEBUGGING PROCESS, I'VE DETERMINED THAT "AREAS.COUNT" = 2.
GIVEN THE ABOVE, I MUST SAY THAT I'M AT A LOSS FOR HOW IT WAS
DETERMINED THAT THERE ARE 2 AREAS, WHEN THERE ARE NO BLANKS ANYWHERE IN
THE DATA. MY ONLY POSSIBLE EXPLANATION IS THAT THE 2 COMES FROM THE
FACT THAT COLUMN1 IS SELECTED, REPRESENTING 1, AND THE OTHER COLUMNS
WITHIN THE CURRENT REGION REPRESENT THE 2ND AREA.

LASTLY, AND THIS IS RELATED TO THE ABOVE, "AREAS(j).CELLS.COUNT" ALSO
EQUALS 2. HOW IS THIS DERIVED?

All help is genuinely appreciated.

Mike

With shtData.Range("A2").CurrentRegion.Columns(1)
..AdvancedFilter Action:=xlFilterInPlace, Unique:=True
ReDim myList(1 To .SpecialCells(xlCellTypeVisible).Count)
With .SpecialCells(xlCellTypeVisible)
For j = 1 To .Areas.Count
For i = 1 To .Areas(j).Cells.Count
myList(myCount) = .Areas(j).Cells(i).Value
myCount = myCount + 1
Next i
Next j
End With
ActiveSheet.ShowAllData

End With

DATA SAMPLE FOLLOWS:

Washington 1981 898
Washington 1982 813
Washington 1983 600
Washington 1984 168
Washington 1985 419
Washington 1986 1076

[Dave Peterson]

You have a response at your other post.

Takeadoe wrote:
>
> Dear NG members,
>
> I've embedded my questions in the code below.
>
> Sub GraphByUniqueCategory()
> Dim myList() As Variant
> Dim i As Integer
> Dim j As Integer
> Dim myCount As Integer
> Dim chtDeer As Chart
> Dim shtData As Worksheet
> Dim rngData As Range
> Dim myDataSet As Range
> Dim strCounty As String
>
> myCount = 1
>
> Set shtData = Worksheets("Sheet1")
>
> HERE IS WHAT I'VE READ FROM OTHER POSTS REGARDING THE AREAS COLLECTION:
>
> 1) The Range object has an Areas collection that allows you to access
> multiple noncontiguous ranges in a selection. Therefore, if
> Selection.Areas.Count=1, then you know that the selected range is
> contiguous. If Selection.Areas.Count>1 then you can access the
> different parts of the selection with "Dim rngArea As Range"
>
> 2) Each range in the Areas collection is a rectangular range of
> contiguous cells.
>
> FROM THE DEBUGGING PROCESS, I'VE DETERMINED THAT "AREAS.COUNT" = 2.
> GIVEN THE ABOVE, I MUST SAY THAT I'M AT A LOSS FOR HOW IT WAS
> DETERMINED THAT THERE ARE 2 AREAS, WHEN THERE ARE NO BLANKS ANYWHERE IN
> THE DATA. MY ONLY POSSIBLE EXPLANATION IS THAT THE 2 COMES FROM THE
> FACT THAT COLUMN1 IS SELECTED, REPRESENTING 1, AND THE OTHER COLUMNS
> WITHIN THE CURRENT REGION REPRESENT THE 2ND AREA.
>
> LASTLY, AND THIS IS RELATED TO THE ABOVE, "AREAS(j).CELLS.COUNT" ALSO
> EQUALS 2. HOW IS THIS DERIVED?
>
> All help is genuinely appreciated.
>
> Mike
>
> With shtData.Range("A2").CurrentRegion.Columns(1)
> .AdvancedFilter Action:=xlFilterInPlace, Unique:=True
> ReDim myList(1 To .SpecialCells(xlCellTypeVisible).Count)
> With .SpecialCells(xlCellTypeVisible)
> For j = 1 To .Areas.Count
> For i = 1 To .Areas(j).Cells.Count
> myList(myCount) = .Areas(j).Cells(i).Value
> myCount = myCount + 1
> Next i
> Next j
> End With
> ActiveSheet.ShowAllData
>
> End With
>
> DATA SAMPLE FOLLOWS:
>
> Washington 1981 898
> Washington 1982 813
> Washington 1983 600
> Washington 1984 168
> Washington 1985 419
> Washington 1986 1076

--

Dave Peterson