AUTO SELECT A RANGE FOR PIVOT TABLE

[SSJ]

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

 

[Ron Coderre]

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)


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