VBA - Setting a Range and then reducing it by 2 rows - The range is part of a pivot table
Experts,
I have defined my range as the following:
The above sets the range as A2 to A117. I now want to remove the last 2 rows from this range, so that I have A2:A115 (I can not make this a static range as the data will likely change).
Attached is an example (hand typed, not a pivot table) where I would want to set RNG = A2:A11.
Or - is there a method to call A2:A11 (everything underneath PLAN, in my example) via a pivot table property?
EE-Setting-A-Range-Reduce-By-2-E.xls
I have defined my range as the following:
Sub FormatSalesByProduct()
Dim RNG As Range
Set RNG = Range("A2", Range("A65536").End(xlUp))
RNG.select 'for testing to see what cells are in the range
End SubThe above sets the range as A2 to A117. I now want to remove the last 2 rows from this range, so that I have A2:A115 (I can not make this a static range as the data will likely change).
Attached is an example (hand typed, not a pivot table) where I would want to set RNG = A2:A11.
Or - is there a method to call A2:A11 (everything underneath PLAN, in my example) via a pivot table property?
EE-Setting-A-Range-Reduce-By-2-E.xls
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Rory Archibald
If you posted an actual pivot table, it would be easier to verify the other option. ;)
Duh, rory has his brain switched on...
ASKER
Rorya,
I probably should of made a pivot table - so see attached. I used the following:
Is this the best way to do this, or is there a better way that would set the range to everything below PLAN, excluding the Total Lines? My only reason for this is in-case the Pivot was to add an additional Total line or remove one, the Offset(-2) would not work.
Thank you,
Kevin
EE-Setting-A-Range-PivotTable-In.xls
I probably should of made a pivot table - so see attached. I used the following:
Sub FormatSalesByProduct()
Dim RNG As Range
Set RNG = Range("G5", Range("G65536").End(xlUp).Offset(-2))
RNG.Select 'for testing to see what cells are in the range
End SubIs this the best way to do this, or is there a better way that would set the range to everything below PLAN, excluding the Total Lines? My only reason for this is in-case the Pivot was to add an additional Total line or remove one, the Offset(-2) would not work.
Thank you,
Kevin
EE-Setting-A-Range-PivotTable-In.xls
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you so much!!!!
The DataRange from the pivot table is the way I am going to go - makes the most sense and is dynamic!!!
Regards,
Kevin
The DataRange from the pivot table is the way I am going to go - makes the most sense and is dynamic!!!
Regards,
Kevin
ASKER
I am going with:
Sub FormatSalesByProduct()
Dim RNG As Range
Set RNG = ActiveSheet.PivotTables(1).RowFields(1).DataRange
RNG.Select
End Sub
Thanks Kevin - very generous.