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
|
VBA Visual Basic for Applications (Microsoft) FAQ
Excel How To
How can I define one range MINUS another intersecting range by SkipVought
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)
CODEMinus(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...
CODEMinus(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...
CODEFunction 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 |
|
|
|
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:
Talk To Other Members
- Notification Of Responses To Questions
- Favorite Forums One Click Access
- Keyword Search Of All Posts, And More...
Register now while it's still free!
Already a member? Close this window and log in.
Join Us Close