why does Average only on certain cells?

[childofthe1980s]

Hello:

I created a macro that performs, among other things, the Average function in
a spreadsheet. Below is an excerpt from it.

I am perplexed as to why the "Range" value is a limited number of cells. I
did not mean for it to be that way. I highlighted the entire spreadsheet
when I recorded the macro and before I selected to Average. I don't
understand why it did not capture all of the cells.

As you notice, the range is only A1:E1688. I did not mean for that to
happen. How do I modify this so that it encompasses all data in the
spreadsheet (and--by the way--not all of the potentially thousands of blank
rows beneath the data)?

childofthe1980s

Selection.Subtotal GroupBy:=1, Function:=xlAverage, TotalList:=Array(3,
5) _
, Replace:=True, PageBreaks:=False, SummaryBelowData:=True
ActiveSheet.Outline.ShowLevels RowLevels:=2
Range("A1:E1688").Sort Key1:=Range("E2"), Order1:=xlDescending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

 

[Jim Cone]

Subtotaling and Sorting doesn't have much to do with Averaging ?

If you select the data before running the code then replace...

Range("A1:E1688")
-with-
Selection
--
Jim Cone
Portland, Oregon USA




"childofthe1980s"
<[email protected]>
Hello:

I created a macro that performs, among other things, the Average function in
a spreadsheet. Below is an excerpt from it.

I am perplexed as to why the "Range" value is a limited number of cells. I
did not mean for it to be that way. I highlighted the entire spreadsheet
when I recorded the macro and before I selected to Average. I don't
understand why it did not capture all of the cells.

As you notice, the range is only A1:E1688. I did not mean for that to
happen. How do I modify this so that it encompasses all data in the
spreadsheet (and--by the way--not all of the potentially thousands of blank
rows beneath the data)?

childofthe1980s

Selection.Subtotal GroupBy:=1, Function:=xlAverage, TotalList:=Array(3,
5) _
, Replace:=True, PageBreaks:=False, SummaryBelowData:=True
ActiveSheet.Outline.ShowLevels RowLevels:=2
Range("A1:E1688").Sort Key1:=Range("E2"), Order1:=xlDescending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

 

[childofthe1980s]

I don't understand your solution. Could you please elaborate?

You don't have to be concerned about the other part of my code regarding
sorting and subtotaling. That was not my question. I simply posted an
excerpt of my code. Remember. I said that my macro, "among other things",
uses the average function.

childofthe1980s

 

[childofthe1980s]

OK....that worked....thank you, Jim!

I apologize, if the tone of my other reply came off as rude. I just don't
understand why I had to go to the trouble of editing the VBA itself. I told
Excel during my recording session to use all of the data. I don't know how
else I could have been more clear.

I don't understand VBA nor did I understand all of this syntax. But, your
solution did give me the help that I needed.

Again, thank you!

childofthe1980s

 

[Jim Cone]

Think of the macro recorder as being hard of hearing.<g>
Also, as I understand it, XL2007 it is almost deaf in some areas.
(i have my cynical beliefs about why that is)

You can find help and understandable explanations about VBA
in almost any version of the "Power Programming" series of
books by John Walkenbach. Having one of them on the shelf
can eliminate a lot of frustration.
--
Jim Cone
Portland, Oregon USA




"childofthe1980s"
<[email protected]>
wrote in message
OK....that worked....thank you, Jim!

I apologize, if the tone of my other reply came off as rude. I just don't
understand why I had to go to the trouble of editing the VBA itself. I told
Excel during my recording session to use all of the data. I don't know how
else I could have been more clear.

I don't understand VBA nor did I understand all of this syntax. But, your
solution did give me the help that I needed.
Again, thank you!
childofthe1980s