dynamic data range for excel pivot table and excel graph

PeterBaileyUk
PeterBaileyUk used Ask the Experts™
on
I have a sheet that has raw data and then a pivot table based on that data and then a graph based on the pivot table.

How can I in vba in excel expand the range of the pre existing pivot table and graph automatically.

I was using this to clear down the data on open and prior to appending the data so I think its the reverse of that ie get used range and use that value for the pivot and graph

With Sheets("CW data")
    Intersect(.UsedRange, .Range("A:N")).ClearContents
  End With

help would be appreciated hope i explained it ok.

The width of columns remains constant but not the data rows.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2011
Commented:
Easiest is to use a dynamic named range: http://www.contextures.com/xlPivot01.html
no code required.
hi Peter,

I suggest using a Dynamic Named Range (DNR) that you define once (within excel) so that you don't need to repeatedly change or redefine the range when the number of rows change. Here is a link to Debra Dalgleish's page which explains DNR: http://www.contextures.com/xlNames01.html

The above link explains using Offset & Counta, however I prefer to use an Index & Counta approach which is discussed on: http://www.contextures.com/xlNames03.html

I've had problems with people deleting rows etc which upsets named ranges, so here is an example of a group of named ranges that I sometimes use to try & be more robust (I know it's overkill & I imagine it could be simplified a lot, but it may help):

Name =...
SOH_TopLeftHdrCell ='SOH'!$A$6
SOH_ColToCount ='SOH'!$A:$A
SOH_EntireSheet =INDEX('SOH'!$A:$A,,):INDEX('SOH'!$1:$1,,)
SOH_HdrRow =SOH_TopLeftHdrCell:INDEX(SOH_EntireSheet,SOH_NumOfHdrRws,)
SOH_LCol =COUNTA(SOH_HdrRow)
SOH_LRow =SOH_NumOfHdrRws+SOH_NumOfDataRws
SOH_NumOfDataRws =COUNTA(SOH_ColToCount)-COUNTA(INDEX(SOH_ColToCount,1):INDEX(SOH_ColToCount,SOH_NumOfHdrRws))
SOH_NumOfHdrRws =ROW(SOH_TopLeftHdrCell)
SOH_DynSourceDataForPT =INDEX('SOH'!$A:$A,SOH_NumOfHdrRws):INDEX(SOH_EntireSheet,SOH_LRow,SOH_LCol)
SOH_CurCol =INDEX('SOH'!A:A,SOH_NumOfHdrRws):INDEX('SOH'!A:A,SOH_LRow)

Open in new window

hth
Rob
Ooopps, I should have refreshed my page before posting. At least you are getting consistent suggestions & I'm thinking along the same lines as Rory (if you ignore the verbosity of my example!).

Actually, embarrassingly, I've just realised that if column A is deleted, a lot of the Names in my example cause #ref! errors, so my example isn't just verbose... it is not even effective in its goal of "delete happey users" :-(

Rob
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
If you want to change the range in VBA, here is some sample code that does so in Excel 2010:

Sub NewPTrange()
Dim pc As PivotCache
Dim pt As PivotTable
Dim rg As Range
Dim v As Variant
Dim ws As Worksheet
Set pt = ActiveSheet.PivotTables(1)
Set pc = pt.PivotCache
v = Split(pc.SourceData, "!")
Set ws = Worksheets(v(0))
Set rg = ws.Range(Application.ConvertFormula(v(1), xlR1C1, xlA1))
Set rg = rg.Cells(1, 1).CurrentRegion
pc.SourceData = v(0) & "!" & rg.Address(True, True, xlR1C1)
End Sub

Open in new window

Top Expert 2010
Commented:
I cover this topic in my article here: http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/A_3172-How-to-Automatically-Update-Your-PivotTables-and-PivotCharts.html

It demonstrates techniques to do two things:

1) Automatically adjust the source range of your PivotTable/PivotChart as you add more rows/columns

2) Automatically refresh the PT/PC to make sure it is always using the "latest and greatest" data

Like the Experts above, I recommend using a dynamic Name for your pivot source range if you are in Excel 2003.  In Excel 2007/2010, I recommend using a Table as the source: Tables already dynamically expand to accept contiguous data.

Author

Commented:
I have followed the above links but am using excel 2010 and cannot see how to name the range dynamically as the menu choices they give are not the same and i cannot find them in 2010
Most Valuable Expert 2011
Top Expert 2011

Commented:
Formulas tab, Name Manager, click 'New...' then set up the name as directed. :)

Author

Commented:
I see  matthewpatrick suggests tables for 2010 not sure where it shows how to use those, the data is imported from an access query.

Author

Commented:
I found the tables part just experimenting now.

Author

Commented:
I have added some code that turns the rows into a table on the worksheet courtesy of an EE expert. I am not sure how to amend the existing pivot table to use the table I just created as the link you showed shows what to do if creating the pivot table from new.

Author

Commented:
ok I created the pivot table using the table 1 as the data source so is that enough now, I assume as its a table the table will dynamically expand and contract automatically and as the pivot table is linked to the table the process now occurs.

on opening the spredsheet the db clears out the raw data and repopulates and then creates the tables will that be a problem for the existing pivot table?
It shouldn't cause a problem as long as the table is re-populated before the pivot-table gets refreshed. If the pivot-table is refreshed when there is no data you may get an error along the lines of "a pivot table must have at least two rows as its source data".

Rob

Author

Commented:
Ok i wasnt sure as it mentioned refreshing of ptcache in the instructions provided above.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial