AUTO SELECT A RANGE FOR PIVOT TABLE
Hello,
Here is the situation: 1) I have a download (from the accounting system) of data that I use to create a pivot table. 2) The download is never of fixed rows. Lines could added or subtracted to the download. 3) Some columns need to be clean using clean & trim functions 4) I utilize MACRO to accomplish the above. Because of change in the number of rows the range programmed in the macro is always more or less than the actual range needed. Hence, I have to go in and manually clean/trim the cells outside the range in the data and then reselect the range from the pivot table. How can I make the range selection flexible. Thanks SJ |
AUTO SELECT A RANGE FOR PIVOT TABLE
You could base the pivot table on a named range.
Then you could either: 1) Have the macro redefine that named range's reference. or 2) Base the pivot table on a Dynamic Named Range (which automatically expands and contracts to accommodate the available data) Debra Dalgleish's website has instructions: http://www.contextures.com/xlNames01.html#Dynamic Does that help? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "SSJ" wrote in message ... Hello, Here is the situation: 1) I have a download (from the accounting system) of data that I use to create a pivot table. 2) The download is never of fixed rows. Lines could added or subtracted to the download. 3) Some columns need to be clean using clean & trim functions 4) I utilize MACRO to accomplish the above. Because of change in the number of rows the range programmed in the macro is always more or less than the actual range needed. Hence, I have to go in and manually clean/trim the cells outside the range in the data and then reselect the range from the pivot table. How can I make the range selection flexible. Thanks SJ |
All times are GMT +1. The time now is 08:17 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com