Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table & Dynamic data
I am importing data of variable length (rows) and never know the exact size.
How can I write a programme that in setting up the Pivot table identifies the size of the data table in the same way that "currentregion" does for say copy and paste. I am told that dynamic range is the answer but dont know hoe to write syntax to incorporate into the pivot element of the VBA programme. Ta in advance for any help offerred |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table & Dynamic data
You could use .current region in your code, too.
dim myPTRng as range with worksheets("Sheet1") set myptrng = .range("a1").currentregion end with Or if you know the last column (say W) and can pick out a column that always has an entry (I used column A in my example): dim myPTRng as range with worksheets("Sheet1") set myptrng = .range("a1:W" & .cells(.rows.count,"A").end(xlup).row) end with then later... ,sourcedata:=myptrng.address(external:=true), ... If the data can change after you create the pivottable, you may want to look at using a dynamic range. You can see some instructions at Debra Dalgleish's site: http://www.contextures.com/xlNames01.html#Dynamic Sophism wrote: I am importing data of variable length (rows) and never know the exact size. How can I write a programme that in setting up the Pivot table identifies the size of the data table in the same way that "currentregion" does for say copy and paste. I am told that dynamic range is the answer but dont know hoe to write syntax to incorporate into the pivot element of the VBA programme. Ta in advance for any help offerred -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
use dynamic data source to make pivot table | Excel Discussion (Misc queries) | |||
vlookup data in a dynamic pivot table? | Excel Worksheet Functions | |||
pivot table with dynamic base data | Charts and Charting in Excel | |||
Pivot table, dynamic data formula | Excel Discussion (Misc queries) | |||
Update Data Field in Pivot Table with Dynamic Excel Range | Excel Programming |