I have instances like a PivotTable that always returns the same number of columns, but a variable number of rows.
I have formulas immediately to the right of the PivotTable, base on values in the PivotTable, and I want to adjust the formulas in accordance with the number of rows in the PivotTable after it is refreshed. Unlike the Data > Import External Data feature, which can "Fill down formulas in columns adjacent to data", there is no such feature for PivotTables; you must do it yourself.
I want to keep one row of formulas and delete the remainder. Then AutoFill the formulas to the proper row. Is there a nifty method to define the range I want to work on?
This function returns a range and it works like this...
would select the matter to the right of the Pivot Table and below rows 1 to 4.
So here's the nifty function...
CODE
Function Minus(rng1 As Range, rng2 As Range, Optional HrzVrt As Boolean = False) As Range 'SkipVought 2009 Feb 11 '-------------------------------------------------- ':returns a range defined by rng1 minus rng2, _ based on either a Horizontal orientation [FALSE] _ or a Vertical orientation [TRUE] '-------------------------------------------------- Dim r1 As Range, r2 As Range
Set Minus = Intersect(rng1, rng2)
With rng1 Select Case HrzVrt Case True Set Minus = Range( _ Cells(Minus.Row + Minus.Rows.Count, .Column), _ Cells(.Row + .Rows.Count - 1, .Column + .Columns.Count - 1)) Case False Set Minus = Range( _ Cells(.Row, Minus.Column + Minus.Columns.Count), _ Cells(.Row + .Rows.Count - 1, .Column + .Columns.Count - 1)) End Select End With End Function
Talk To Other Members