Areas Collection Question
 

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.Count1 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

Areas Collection Question
 

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.Count1 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