Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    May 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    87
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro to define Autofilter range (2003)

    Hi everyone,

    Q. How do you define the autofilter range in a macro?

    I have encountered the problem where an autofilter's range spreads beyond the rows of data I want filtered. I often have "totals" at the bottom of my data and these are automatically included in the filtered range. I have tried to define the autofilter name range, but Excel seems to reset that.

    I gather that I can define the autofilter range in a macro, but I am having difficulty with my limited macro skills. I would ideally like to define the autofilter range based on a named range in the worksheet. e.g. "DataArea" in "Sheet1"

    I can attach an example worksheet if that helps.

    Thanks,

    Matthew

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Macro to define Autofilter range (2003)

    Have you tried using the List feature? Select your data and press Ctrl+L to convert it to a list. You then have an optional totals row that you can toggle on and off and automatic filtering plus a data entry row for adding new data. Otherwise I think you may have to leave a blank row between your data and the totals row in order for the code not to include it in the filter.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Star Lounger
    Join Date
    May 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    87
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to define Autofilter range (2003)

    Thanks Rory,

    That has done exactly what I want. I have never looked into the List function....I need to explore more,.

    Regards,

    Matthew

Posting Permissions

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