Link to home
Start Free TrialLog in
Avatar of PeterBaileyUk
PeterBaileyUk

asked on

dynamic data range for excel pivot table and excel graph

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.
ASKER CERTIFIED SOLUTION
Avatar of Rory Archibald
Rory Archibald
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
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

SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of PeterBaileyUk
PeterBaileyUk

ASKER

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
Formulas tab, Name Manager, click 'New...' then set up the name as directed. :)
I see  matthewpatrick suggests tables for 2010 not sure where it shows how to use those, the data is imported from an access query.
I found the tables part just experimenting now.
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.
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
Ok i wasnt sure as it mentioned refreshing of ptcache in the instructions provided above.