×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here
Home > Forums > Programmers > Languages > VBA Visual Basic for Applications (Microsoft) > FAQs

VBA Visual Basic for Applications (Microsoft) FAQ

Excel How To

How can I define one range MINUS another intersecting range by SkipVought
faq707-7135
Posted: 11 Feb 09

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
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close