Pivot Table - Can you automatically determine range of sourc

Mopacs

New Member
Joined
Mar 6, 2002
Messages
33
Hello there,

I have a question regarding the creation of a pivot table. Is there a way to have excel (macro preferably) automatically determine the range of the source data (for a pivot table) without you having to specify a set coordinates (ie, A2:G2032). In this case the source data will fluctuate in the number of rows every week when I create this pivot table. So one week it may have 2000 rows, the next 1934 rows of data. Any suggestions would be greatly appreciated.

Thanks,

Rob
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
The best approach is to create a named range (e.g., Database) and create your PivotTable using that range. You can either update there reference to this range as new data is added or make it dynamic.
This message was edited by Mark W. on 2002-03-11 11:51
 
Upvote 0
Good suggestions.

Ok, lets say I specify the source data as a named range. What would be an example of the VBA code, which would dynamically determine the extent of the range each time?

Thanks,

Rob
This message was edited by Mopacs on 2002-03-11 11:59
 
Upvote 0
On 2002-03-11 11:57, Mopacs wrote:
Good suggestions.

Ok, lets say I specify the source data as a named range. What would be an example of the VBA code, which would dynamically determine the extent of the range each time?

Thanks,

Rob
This message was edited by Mopacs on 2002-03-11 11:59

No VBA code is required. You either update the data list using Excel's built-in data form (i.e., the Data | Form... menu command) or you define a name that refers to a formula that examines the key field(s) of your data list to determine the extent of the data list.
This message was edited by Mark W. on 2002-03-11 14:44
 
Upvote 0
On 2002-03-11 14:13, Mark W. wrote:


No VBA code is required. You either update the data list using Excel's built-in data form (i.e., the Data | Form... menu command) or you define a name that refers to a formula that examines the key field(s) of your data list to determine the extent of the data list.
This message was edited by Mark W. on 2002-03-11 14:44

Well you'll have to excuse my lack of knowledge here, but do you have an example of a formula that would examine the key fields of a range? I'm not sure I completely understand how to do that. So you create a named range?

Thanks,

Rob
 
Upvote 0
Here is a sample of how to assign a dynamic named range.

Dynamic Range Formula
=Sheet1!$A$1:OFFSET(Sheet1!$A$1,COUNTA(Sheet1!$A$1:$A$65536)-1,COUNTA(Sheet1!$1:$1)-1)
Note: $A$1= Anchor Cell Address (Change as needed)
Sheet1!$1:$1 Anchor Row Number (Change as needed)
The above formula gives a dynamic range that expands both by rows and columns, using the entire column or row. To limit the number of rows, change 65536 to a lower number (ex. 1000 will limit the range to 999 rows) To limit the number of columns, change the Sheet1!$1:$1 to Sheet1!$A$1:$Col :$1, where Col is the column letter of the last column you want in the range. To use, Choose Insert Name Define. Type a name for your range in the name box and then enter the above formula (with any adjustments) in the refers to field. Change Sheet1! in the formula as necessary
This message was edited by lenze on 2002-03-12 09:04
This message was edited by lenze on 2002-03-12 09:05
 
Upvote 0
On 2002-03-12 08:59, Mopacs wrote:
Well you'll have to excuse my lack of knowledge here, but do you have an example of a formula that would examine the key fields of a range? I'm not sure I completely understand how to do that. So you create a named range?

Thanks,

Rob

Can you provide a sample (10-15 rows) of your data including headers? An easy way to do this is to select an unused cell, type =, select a range of records, press F9, and paste the contents of the formula bar into a reply posting.
 
Upvote 0
On 2002-03-12 09:27, Mark W. wrote:

Can you provide a sample (10-15 rows) of your data including headers? An easy way to do this is to select an unused cell, type =, select a range of records, press F9, and paste the contents of the formula bar into a reply posting.

Ok, it looks lenze's example above seems to have worked. I understand the logic, and thats basically what I was looking for.. but I'm open to any additional suggestions of course....

Here is a sample cropping of this data/worksheet..not all columns are represented either. But lets just say the first field heading below is in column A..and the Row headers lie in Row 3. I dont know if this is what you were looking for, but here ya go:

{"Age","dob","Age_Cat","Age_Grp","TP","BP";49,19244,"Adult"," 45-64","23","13";82,7314,"Aged"," 80-84","13","13";66,13010,"Aged"," 65-69","13","13";75,9707,"Aged"," 75-79","13","13";61,14802,"Adult"," 45-64","13","13";82,7094,"Aged"," 80-84","13","13";72,11002,"Aged"," 70-74","23","13";67,12698,"Aged"," 65-69","14","13"}

Thank you (all of you) for your assistance here.

Rob
This message was edited by Mopacs on 2002-03-12 09:46
This message was edited by Mopacs on 2002-03-12 09:47
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,089
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top