I've embedded my questions in the code below. Any help is greatly
appreciated.
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")
FIRST, I KNOW THE WITH/END WITH IS SUPPOSED TO BE A TIME SAVER, AND
I'VE LOOKED AT EXAMPLES WHERE THE TIME SAVINGS IS OBVIOUS, BUT I DON'T
SEE IT HERE. WHAT WOULD THE CODE HAVE LOOKED LIKE WITHOUT THE WITH/END
WITHS?
FINALLY, THE THING THAT I'M HAVING THE MOST TROUBLE WITH IS THE USE OF
THE AREAS COLLECTION.
HERE IS WHAT I'VE READ FROM OTHER POSTS IN THE NG:
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?
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