Change "CurrentRegion" to "CurrentRegion+1 Row"

[VBAProIWish]

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?

 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

 

[Joe4]

See if this works for you:

    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))

 

[VBAProIWish]

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]

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]

Ok, yes, I believe that you are correct and that's what I will do.

Thanks again

 

[Joe4]

You are welcome.