VBA PivotTable Automatic Range-selection

**Brullie** · 06-20-2013, 03:27 AM

Hi

I've recorded a Macro for generating a PivotTable.
By recording the macro Excel took "WERE!R1C1:R20466C14", however I need it to select the actual datarange which can be bigger but also smaller.
How do I adjust it the code so it will select the actual Data Row/Column size?

Please Login or Register  to view this content.

Thanks!

**apo** · 06-20-2013, 04:18 AM

Hi..

You need to add some code that will detect the bottom of your range..

Lets say your data is from Column A:J.. starting in row 2.. but the number of rows can change..

In this case.. you need to find the last used row in your data range..

First Dimension your variable..

Please Login or Register  to view this content.

then.. to find the Last Row (assuming you have data in Column A all the way till the bottom)..

Please Login or Register  to view this content.

Then... Your Pivot Table code can be..

Please Login or Register  to view this content.

Be careful using..

Please Login or Register  to view this content.

As that will limit the Excel versions your Pivot table will work in.. but if its only for your local machine.. thats fine..

Just noticed you might need to find the last used column...

This is the most reliable thing for that..

Please Login or Register  to view this content.

**JosephP** · 06-20-2013, 05:03 AM

perhaps

Please Login or Register  to view this content.

**Brullie** · 06-20-2013, 06:53 AM

Thank you, works excellent!

VBA PivotTable Automatic Range-selection

LinkBack

Thread Tools

Rate This Thread

Display

VBA PivotTable Automatic Range-selection

Re: VBA PivotTable Automatic Range-selection

Re: VBA PivotTable Automatic Range-selection

Re: VBA PivotTable Automatic Range-selection

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions