Results 1 to 9 of 9
  1. #1
    Lounger
    Join Date
    Feb 2001
    Location
    Glasgow, Scotland
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Have the autofilter function work across columns (2000)

    Is it possible to use have an autofilter type function but rather than work across columns work across rows.

    While looking for this found the highlighter function for autofilter really cool adition

    Cheers

    Angus

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

    Re: Have the autofilter function work across columns (2000)

    I'm afraid not. All filter operations work with database-like tables, where rows are records and columns are fields.

  3. #3
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Have the autofilter function work across columns (2000)

    Hi Angus
    The attached file may be of assistance to you.

    1. <LI>Select the range which you wish to filter;
      <LI>Run the Horiz_Filter macro
      <LI>You will now be presented with your data transposed on a new sheet, and ready to filter;
      <LI>Filter as suits your needs;
      <LI>Run the Reposn macro
      <LI>The sheet which you used for filtering will now be replaced by a new sheet with the filtered results in the original orientation.
    Attached Files Attached Files
    Regards
    Don

  4. #4
    Lounger
    Join Date
    Feb 2001
    Location
    Glasgow, Scotland
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Have the autofilter function work across columns (2000)

    Thanks Don, Just needed to change the field that I was sorting on as it was a formula when it moved into the other worksheet. it became #Ref. However it was one that didn't need to be so still.

    cheers

    Angus

  5. #5
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Have the autofilter function work across columns (2000)

    Don,

    Very clever and useful.

    Just a word of warning to users: If you try to run this a 2nd time, step #5 will cause an error because the Reposn macro renames the sheet created for the data the same "Filtered Source Data" each time. So, rename the sheet created before running this step.

    I do like it.

    Fred

  6. #6
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Have the autofilter function work across columns (2000)

    Angus
    Thank you for the comment. The attached version addresses both the existence of formulae in the selected range, and the point which Fred raised. If you wish to collect the results from several filter criteria you can do so. The results will take the first available number in the range of 001 to 999.
    Attached Files Attached Files
    Regards
    Don

  7. #7
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Have the autofilter function work across columns (2000)

    Hi Don,

    Thanks for the fixes.

    It occurs to me that a few simple additions to the code would make this even more useful. I'm not suggesting you do anything, since I could do it, when/if my need arises, and the original request was for a horizontal filter.

    The simple additions would be an input box that asked if the data were arranged horizontally (as now) or vertically. Given the response, the Transpose arg would be set to true or false in the first macro and a global variable would be set to the answer for use in the Reposn macro with the Transpose arg.

    What this allows one to do is multiple filtering against the original data with each filtered data set being saved in a new sheet while not having to turn off filtering on the original data to run a second filter.

    Just wanted to ask if I was missing anything in this as far as the code goes.

    Fred

  8. #8
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Have the autofilter function work across columns (2000)

    Hi Fred
    I would not be inclined to do that. The end result of your scheme can be achieved with Edit > Move or Copy Sheet . . ., then checking the Create a copy box in the resulting Move or Copy pane.
    Attached Images Attached Images
    Regards
    Don

  9. #9
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Have the autofilter function work across columns (2000)

    Don,

    The result of your latest suggestion creates a copy of the sheet with the hidden rows from the original (ie, those not satisfying the filter criteria) still present in the copy, and the filter drop downs still present in both the original and the copy. At least in 2003.

    Your macro copies only the result of the filter, not any of the hidden rows - a more desirable outcome IMHO. It also leaves the original and filtered data untouched in terms of the filter drop downs.

    Fred

Posting Permissions

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