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:

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 Sub

Open in new window


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
LVL 23
redrumkevAsked:
Who is Participating?
 
Rory ArchibaldCommented:
For that I would use:
ActiveSheet.PivotTables(1).RowFields(1).DataRange

Open in new window

0
 
Rory ArchibaldCommented:
Why not just:
Set RNG = Range("A2", Range("A65536").End(xlUp).Offset(-2))

Open in new window

0
 
StephenJRCommented:
This perhaps, though not sure if it will work on a PT:

Set RNG = Range("A2", Range("A65536").End(xlUp))
Set RNG = RNG.Resize(RNG.Rows.Count - 2)
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Rory ArchibaldCommented:
If you posted an actual pivot table, it would be easier to verify the other option. ;)
0
 
StephenJRCommented:
Duh, rory has his brain switched on...
0
 
redrumkevAuthor Commented:
Rorya,

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 Sub

Open in new window


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
0
 
redrumkevAuthor Commented:
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
0
 
redrumkevAuthor Commented:
I am going with:

Sub FormatSalesByProduct()

Dim RNG As Range

Set RNG = ActiveSheet.PivotTables(1).RowFields(1).DataRange
RNG.Select

End Sub

Open in new window

0
 
StephenJRCommented:
Thanks Kevin - very generous.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.