Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Areas Collection Question

  Asked By: Matthew    Date: Aug 16    Category: MS Office    Views: 913
  

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

Share: 

 

1 Answer Found

 
Answer #1    Answered By: Fabia Ferrrari     Answered On: Aug 16

There are quite a few usages of the With item in the cod, including in the
second With statement itself.

Every time  you see a full-stop preceded by a space it will be using a With.
In this case, the two With clauses are:

With shtData.Range("A2").CurrentRegion.Columns(1)
With .SpecialCells(xlCellTypeVisible)

So, the first With stops the need to repeat
shtData.Range("A2").CurrentRegion.Columns(1) several times, and the second
With stops the need to repeat
shtData.Range("A2").CurrentRegion.Columns(1).SpecialCells(xlCellTypeVisible)
several times.

This shortens the code  considerably and also assists the VBA engine to
create a temporary reference and use that - thereby speeding up execution
too.

The item you are checking the areas count  on is
.SpecialCells(xlCellTypeVisible) which is the visible cells  after the filter
has been created. The filter has presumably filtered out one or more rows.
(As the count is 2, I'd assume that there is only one gap.)

The area  you are checking is one column wide, so I can also assume that one
of the two visible areas (I don't know which one, because you haven't said
what "j" is when the count equals 2) is two rows high.

 
Didn't find what you were looking for? Find more on Areas Collection Question Or get search suggestion and latest updates.




Tagged: