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

Minus(RangeToSubtractFrom, RangeToSubtract, HorizontalOrVerticalBoolean)

CODE

Minus(ActiveSheet.UsedRange, Rows("1:2"), True).select

would select all the Used Range except for rows 1 & 2.

Then combining that range with the Pivot Table range...

CODE

Minus(Minus(ActiveSheet.UsedRange, Rows("1:4"), True), ActiveSheet.PivotTables(1).TableRange1.EntireColumn, False).Select

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

Back to VBA Visual Basic for Applications (Microsoft) FAQ Index
Back to VBA Visual Basic for Applications (Microsoft) Forum

My Archive

My FAQ Archive

Come Join Us!

Posting Guidelines

Jobs

VBA Visual Basic for Applications (Microsoft) FAQ

Excel How To

CODE

CODE

CODE

My Archive

Join Tek-Tips® Today!

Join Tek-Tips^® Today!