Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
why does Average only on certain cells?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
why does Average only on certain cells?
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" 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
why does Average only on certain cells?
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 "Jim Cone" wrote: 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" 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
why does Average only on certain cells?
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" wrote: 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" 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
why does Average only on certain cells?
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" 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 "Jim Cone" wrote: 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" 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need to average data if in cells, but ignore blank cells. | Excel Worksheet Functions | |||
zero value cells/blank cells causing error in AVERAGE? | Excel Worksheet Functions | |||
from a group of cells.find average of cells containing values | Excel Discussion (Misc queries) | |||
average cells, show 0 if nothing to average | Excel Discussion (Misc queries) | |||
average 2 cells (Mileage Divide by Gallons in two cells | Excel Discussion (Misc queries) |