Change "CurrentRegion" to "CurrentRegion+1 Row"

VBAProIWish

Well-known Member
Joined
Jul 6, 2009
Messages
980
Hello All,

I have this code below that selects the CurrentRegion in a pivot table/cache.

How can I add one row to the current region in the below code?


Rich (BB code):
 ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveSheet.PivotTables("PivotTable2").ChangePivotCache ActiveWorkbook. PivotCaches.Create(SourceType:=xlDatabase, _
    SourceData:=Worksheets("SDB").Range("A1").CurrentRegion) '<<< change to "CurrentRegion" PLUS ONE ROW


Thanks much
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,020
Office Version
  1. 365
Platform
  1. Windows
See if this works for you:
VBA Code:
    Dim cr As Range
    Set cr = Worksheets("SDB").Range("A1").CurrentRegion
    
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveSheet.PivotTables("PivotTable2").ChangePivotCache ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
        SourceData:=cr.Resize(cr.Rows.Count + 1, cr.Columns.Count))
 
Solution

VBAProIWish

Well-known Member
Joined
Jul 6, 2009
Messages
980
Thank you very much for that code!

It works and I can use it, one minor caveat to it is that it removes groupings/filters (by year) I had, resulting in me having to re-group by year and then re-filter out the years I don't want to see.

Not a huge deal though and I can workaround it by re-grouping/re-filtering.

If you have some magical piece of code that I can insert to prevent the un-grouping/un-filtering to happen, that would be great.

If not, I am good and again thank you much!
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,020
Office Version
  1. 365
Platform
  1. Windows
I may be wrong, but I think that maybe because you are changing the size of the original range that the filters were applied to, it automatically removes the old filters.
I think the way around that is to just reapply them. You may be able to get the VBA code required to do that using the Macro Recorder.
 

VBAProIWish

Well-known Member
Joined
Jul 6, 2009
Messages
980
Ok, yes, I believe that you are correct and that's what I will do.

Thanks again
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,020
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
 

Forum statistics

Threads
1,141,400
Messages
5,706,217
Members
421,433
Latest member
yash0468

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
Top