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 |
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 |
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 |
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 |
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 |
All times are GMT +1. The time now is 10:41 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com