Results 1 to 4 of 4

Thread: Range name (97)

  1. #1
    Star Lounger
    Join Date
    Apr 2001
    Location
    SOUTH AFRICA
    Posts
    74
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Range name (97)

    Hi there,

    I trust one of you Guru's will be able to help me. I want to create a range name which will exclude hidden cells, ie: if I do an autofilter, the range name must only include the filtered cells. My aim is to use the named range for my pivot tables. The user will filter the database, then click on the commandbutton which will create a pivot table for the filtered data.

    Thanks in advance.

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

    Re: Range name (97)

    Hi Rene,

    I added a sample workbook to demonstrate what I did.

    Summarized:

    - created a defined name called RowVisible that checks whether a row is hidden or not
    - added a column to the right of the list to be filtered with the formula
    =RowVisible
    and included that in the autofilter
    - defined a name called Database that automatically adjusts for the number of rows in the data (You will have to cahnge the last 3 in that defined name to adjust for your amount of columns)
    - defined a PT report based on the rangename Database.
    - Added RowVisible as one of the grouping cells.

    Clear as mud??
    Attached Files Attached Files
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Star Lounger
    Join Date
    Apr 2001
    Location
    SOUTH AFRICA
    Posts
    74
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Range name (97)

    Hi there clever man,

    Thank you very much for the solution. If possible, please explain the =rowvisible formula. (especially the 17 in the formula.

    Thanks once again.

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

    Re: Range name (97)

    Hi Rene,

    Get.Cell is an ancient xlm macro function. The argument 17 yields the height of the row the cell is in, in points.
    The INDIRECT part ensures the formula always works on the cell the formula is called from.
    Hidden rows always have a rowheight equal to zero.
    For some more wizardry with xlm macro functions in defined names, check out my arg2name.zip on:
    http://www.bmsltd.ie/mvp

    Edited Mar 13th 2004 to update link
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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