VBA code for Dynamically Updating Range

Question

I have a Excel Data sheet which has static Columns but regularly increasing Columns, can anyone help me out with VBA code that can update the range Dynamically, and it would be great if it can update the same in Pivot Table too..

Unfortunately our SAP team made a mess with headers, i should take the data from row A2 rather than A1.

Thanks a lot in advance.

Answer 1

Hi:

I have designed the same using Name manager. 

=OFFSET(Sheet3!$A$2,0,0,COUNTA(Sheet3!$A$2:$A$15),COUNTA(Sheet3!$A$2:$Q$2))

Define a dynamic range and you can give the same range in Pivot table...

Regards,

Kiran

*** Email address is removed for privacy ***

Hi Kiran,

Thanks for the help, it is really useful. i have used SpecialCells Method it is working fine with Dynamic range now.

But unfortunately i got stuck with charts. My data sheet has an Pivot table and Pivot chart assigned, unfortunately only the Pivot table is being updated but not the charts. could you help me with this .

Thank you

Answer 2

Hi:

I have designed the same using Name manager. 

=OFFSET(Sheet3!$A$2,0,0,COUNTA(Sheet3!$A$2:$A$15),COUNTA(Sheet3!$A$2:$Q$2))

Define a dynamic range and you can give the same range in Pivot table...

Regards,

Kiran

******@gmail.com