Results 1 to 9 of 9
  1. #1
    Lounger
    Join Date
    Dec 2001
    Location
    Br. Columbia, Canada
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA equivalent of Advanced Filter? (Excel 97)

    I use advanced filter frequently but find that the requirement for criteria and extract on the same sheet as the filtered list is a major limitation. Let's say I have records in a table which has 5 columns ( Division, Department, Product, Account and Amount ) and want to extract a list of records matching Division, Department and Product criteria from a very large master list.
    Is there a way of doing this through VBA? Most of the material I have read suggests that this can be done through loops but only gives some pretty lame examples such as compiling a list of file names from a folder.
    Any suggestions would be much appreciated.

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

    Re: VBA equivalent of Advanced Filter? (Excel 97)

    If you have "a very large master list", looping through records in VBA is going to be very slow.

    You can use Microsoft Query to get filtered data:
    Select Data/Get External Data/Create New Query...
    Select Excel files and make sure that the Wizard check box (don't know the exact caption in English) is ticked.
    Click OK. Follow the steps of the wizard. YOu can select a file, select fields (columns) to be displayed, set a filter and set a sort order.

    (Note: the criteria range for advanced filter may be on a different sheet; the extract range must be on the same sheet as the original data, unfortunately.)

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: VBA equivalent of Advanced Filter? (Excel 97)

    You can work around the adv filter not being able to put into a different page.

    In VB run the adv filter and put it into an "out-of-the-way place" in the active sheet, then in VB MOVE the entire section to another sheet.

    It is not elegant, but it is faster than writing the code to do all the adv filter.

    Steve

  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 equivalent of Advanced Filter? (Excel 97)

    AFAIK you can have the list and the criteria on different sheets by naming the criteria range (Insert, name, define) and using the NAME as the criteria range (=TheName).
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: VBA equivalent of Advanced Filter? (Excel 97)

    Hans,

    With some trickery you can have your List, Criteria and Extract on 3 different sheets.

    For the Extract set up your column headings (or a subset you want to filter on). Select these and a row of blank columns beneath. You vcan then start the auto filter wizard, set Copy to another location to on, and point the three range selectors as required.

    IN VBA it can also be done simply by setting the three ranges to the appropriate addresses on different sheets (Include the Sheet name of course when specifying the ranges) , and if set up properly it would not matter which sheet is active when the code is run.

    Andrew

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: VBA equivalent of Advanced Filter? (Excel 97)

    An excellent TIP!
    I have tried doing it manually (criteria on another page works, but not the output range) but never tried in VB.
    Seems to work fine in VB.

    Steve

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

    Re: VBA equivalent of Advanced Filter? (Excel 97)

    Andrew,

    Thanks, it works! I had never thought of starting with the extract range...
    Another useful trick to add to the bag.

  8. #8
    Lounger
    Join Date
    Dec 2001
    Location
    Br. Columbia, Canada
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA equivalent of Advanced Filter? (Excel 97)

    Thank you all for your help.
    Andrew, thanks for your solution which was right out of the proverbial box. What is the logic behind this?
    I was successful in implementing your solution manually but encountered 2 minor difficulties when using the macro recorder to convert it to VBA.
    Firstly, how do you get rid of the "The destination range is not large enough" message without manual intervention?
    Secondly, the macro leaves the second row of the extract range (formerly the blank line) highlighted in black even through the macro ends with selecting a cell in the criteria range. Stepping through the macro highlights the first row and then switches the highlight to the second row. The highlight appears to be a selection because it also prevents any single cell in the row from being selected.

  9. #9
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: VBA equivalent of Advanced Filter? (Excel 97)

    1 < how do you get rid of the "The destination range is not large enough">

    Sorry but I should have been clearer. When you specify the Copy to range, you should just include a single row. IN the method I described, you would have initially selected 2 rows, but when filling in the copy to reduce the selection to a single row.

    2. <Secondly, the macro leaves the second row of the extract range (formerly the blank line) highlighted in black>

    Not sure what is going on here but is probably related to 1 above.

    It is always a good idea to reconstruct recorded macros if you intend to use them as code fragments. I would suggest the following code

Posting Permissions

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