[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 357
  • Last Modified:

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
0
redrumkev
Asked:
redrumkev
  • 3
  • 3
  • 3
3 Solutions
 
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
 
Rory ArchibaldCommented:
If you posted an actual pivot table, it would be easier to verify the other option. ;)
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
Rory ArchibaldCommented:
For that I would use:
ActiveSheet.PivotTables(1).RowFields(1).DataRange

Open in new window

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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now