M
mtonkovich
Dear Group - I would imagine your tired of reading my posts by now, but
I'm trying to learn a lot in a short time. I appreciate the help that
all have given. I was given this code and I can follow most of what it
does, but I have a couple of questions that I was hoping I could bother
you once again with.
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")
MY FIRST QUESTION - WAS THERE ANYTING SIGNIFICANT ABOUT THE CHOICE OF
"A2"? FROM WHAT I CAN GATHER, IT APPEARS NOT. I CHANGED IT TO A10 AND
GOT THE SAME RESULT.
THE SECOND HAS TO DO WITH THE WITH/END WITH CONSTRUCT. I KNOW ITS
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?
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
I'm trying to learn a lot in a short time. I appreciate the help that
all have given. I was given this code and I can follow most of what it
does, but I have a couple of questions that I was hoping I could bother
you once again with.
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")
MY FIRST QUESTION - WAS THERE ANYTING SIGNIFICANT ABOUT THE CHOICE OF
"A2"? FROM WHAT I CAN GATHER, IT APPEARS NOT. I CHANGED IT TO A10 AND
GOT THE SAME RESULT.
THE SECOND HAS TO DO WITH THE WITH/END WITH CONSTRUCT. I KNOW ITS
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?
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