Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    St. Charles, Illinois
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA code for end of sheet (Excel 2000)

    I wrote a macro to create a pivot table. The length of the pivot table will vary every month. Currently the range is A1:P1000. What would be a generic code for the end of the sheet? Thank you.

  2. #2
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    Toronto, Ontario, Canada
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA code for end of sheet (Excel 2000)

    The maximum number of rows is: 65,536 (or 2^16)

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA code for end of sheet (Excel 2000)

    If you want to get a reference to the table containing A1, you can use Range("A1").CurrentRegion.

    Alternatively, if column A will always be filled, you can use

    Dim lngMaxRow As Long
    Dim rng As Range
    lngMaxRow = Range("A65536").End(xlUp).Row
    Set rng = Range("A1:P" & lngMaxRow)

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA code for end of sheet (Excel 2000)

    The .DataBodyRange of a pivot table gives you the range that is occupied by the data area of the PT.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    St. Charles, Illinois
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA code for end of sheet (Excel 2000)

    Another approach that I used was to change the pivot table range in Step 2 of 3 of the Wizard. I selected the range to be A1:P65536. This allowed for growth, but the second to the last line of the Pivot table says "(blank)" on every cell. Is there a way to program the pivot to delete a line that has "(blank)". The blanks throw off the Grand Totals. Thank you.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA code for end of sheet (Excel 2000)

    Interactively, you can right-click (blank) and click Hide in the popup menu. In VBA, it would look like this:

    ActiveSheet.PivotTables(1).PivotFields("MyField"). PivotItems("(blank)").Visible = False

    where MyField is the name of the relevant pivot field.

  7. #7
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    St. Charles, Illinois
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA code for end of sheet (Excel 2000)

    The interactive is great for the macro. thank you.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •