Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How can I define one range MINUS another intersecting range

Excel How To

How can I define one range MINUS another intersecting range

by  SkipVought  Posted    (Edited  )
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

Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top