Hi,
Please use below codes for updates last used row & last used column in
Pivot Tables.
Sheets("Sheet1").Select
ActiveWorkbook.Names.Add Name:="Sheet1", RefersToR1C1:= _
"=OFFSET(Sheet1!C1,0,0,COUNTA(Sheet1!C1),COUNTA(Sheet1!R1))"
ActiveWorkbook.Names("AfterEntry").Comment = ""
Regards,
MARIES.
On Wed, Feb 15, 2012 at 7:18 AM, anandydr <anand...@gmail.com> wrote:
> Although I have not tested it on Excel 2010 in Excel 2007 the shortcut
> key to Name Manager dialog box is Ctrl + F3 I hope it works for 2010
> also.
> As for explanation given by Noorain it's excellent and most complete.
> You can also achieve this through VBA but it would effect the speed/
> performance of your spreadsheet.
>
> Warm Regards,
> Anand Kumar
>
> On Feb 15, 6:15 am, Awal <malaw...@gmail.com> wrote:
> > Thanks Noorain.
> > That was very thorough. I am using excel 2010 but I can find the Name
> > Manager.
> > I will encourage all our Excel gurus to follow your footsteps.
> > This little tutorial made me like this forum even more!!!
> > Thanks a lot and best regards.
> >
> > On Feb 13, 11:11 pm, NOORAIN ANSARI <noorain.ans...@gmail.com> wrote:
> >
> >
> >
> >
> >
> >
> >
> > > Dear Awal,
> >
> > > Please use it
> >
> > > Press CTRL F3 or go to Edit Insert Name Define
> >
> > > Name your range – e.g. pivot_table_data1
> >
> > > In the refers to section type or copy and paste this formulas
> >
> > > =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))
> >
> > > or
> >
> > > =Sheet1!$A$1:INDEX(Sheet1!$A:$F,COUNTA(Sheet1!$A:$A),6)
> >
> > > [image: clip_image002] <
> http://www.spyjournal.biz/files/clip_image002_0.jpg>
> >
> > > Assuming that your data starts in cell A1 of Sheet1. Edit accordingly
> > > before pasting in
> >
> > > What this formula does is creates an array that starts at cell a1 goes
> zero
> > > cells down and zero cells right, and is x cells deep and y cells wide.
> x =
> > > the count of all non blank cells in column A and y the count of all non
> > > blank cells in row 1. Assumes that column A and row 1 are contiguous
> data
> > > blocks the same length and width as the whole data set. If not use
> columns
> > > and rows that are.
> >
> > > Now in your pivot table right click and go to Pivot Table Wizard
> >
> > > [image: clip_image004] <
> http://www.spyjournal.biz/files/clip_image004_1.jpg>
> >
> > > Hit Back and go to the range selection
> >
> > > [image: clip_image006] <
> http://www.spyjournal.biz/files/clip_image006_1.jpg>
> >
> > > Type = and the range name = e.g. =pivot_table_data1
> >
> > > In my case my range name was called test.
> >
> > > Hit Finish and it is done.
> >
> > > Now whenever you paste more data into the data sheet the pivot table
> just
> > > needs to be refreshed to pick it up
> >
> > > Click anywhere on the pivot table and click the exclamation mark in the
> > > toolbar.
> >
> > > The instructions above assume you are using office 2003. In office
> 2007 the
> > > Name Manager is a little more confusing, but can be accessed with the
> same
> > > keyboard shortcut and you will need to create a new range name and
> then use
> > > the same formula listed above.
> >
> > > --
> > > Thanks & regards,
> > > Noorain Ansari
> > > *http://noorainansari.com/*
> > > *http://excelmacroworld.blogspot.com/*<
> http://excelmacroworld.blogspot.com/>
> >
> > > On Tue, Feb 14, 2012 at 6:49 AM, Awal <malaw...@gmail.com> wrote:
> > > > I have code to query a database and return data to the "data" sheet.
> > > > The number of rows of data returned will vary.
> > > > Through VBA code, how do I tell the pivot table to update the range
> of
> > > > the source data (which is really to update the last row).
> >
> > > > (Selecting CTRL-End, goes past where the actual data ends)
> >
> > > > Thank you.
> >
> > > > --
> > > > FORUM RULES (986+ members already BANNED for violation)
> >
> > > > 1) Use concise, accurate thread titles. Poor thread titles, like
> Please
> > > > Help, Urgent, Need Help, Formula Problem, Code Problem, and Need
> Advice
> > > > will not get quick attention or may not be answered.
> >
> > > > 2) Don't post a question in the thread of another member.
> >
> > > > 3) Don't post questions regarding breaking or bypassing any security
> > > > measure.
> >
> > > > 4) Acknowledge the responses you receive, good or bad.
> >
> > > > 5) Cross-promotion of, or links to, forums competitive to this
> forum in
> > > > signatures are prohibited.
> >
> > > > NOTE : Don't ever post personal or confidential data in a workbook.
> Forum
> > > > owners and members are not responsible for any loss.
> >
> > > >
> ---------------------------------------------------------------------------
> ---------------------------
> > > > To post to this group, send email to excel-macros@googlegroups.com
>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> ------------------------------------------------------------------------------------------------------
> To post to this group, send email to excel-macros@googlegroups.com
>
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
