+ Reply to Thread
Results 1 to 17 of 17

Macro using named ranges and advanced filter only works first time

  1. #1
    Registered User
    Join Date
    10-15-2015
    Location
    Hobart, Tasmania
    MS-Off Ver
    2003
    Posts
    11

    Unhappy Macro using named ranges and advanced filter only works first time

    Hi everyone,

    I have put the code below into a macro:

    Dim rg1 As Range
    Dim rg2 As Range
    Dim rg3 As Range
    Dim rg4 As Range
    Dim rg5 As Range

    'HOBART
    Set rg1 = Range("AF53", Range("AF53").End(xlDown))
    Set rg2 = Range("AM53", Range("AM53").End(xlDown))
    Set rg3 = Range("AN53", Range("AN53").End(xlDown))
    Set rg4 = Range("AM53")
    Set rg5 = Range("AN53")
    rg1.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=rg4, Unique:=True
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues

    'LAUNCESTON
    Set rg1 = Range("V53", Range("V53").End(xlDown))
    Set rg2 = Range("AC53", Range("AC53").End(xlDown))
    Set rg3 = Range("AD53", Range("AD53").End(xlDown))
    Set rg4 = Range("AC53")
    Set rg5 = Range("AD53")
    rg1.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=rg4, Unique:=True
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues


    (I was going to put a screenshot of the data here but couldn't make the upload work...)

    Here are the issues:
    1 The first time I run it the Hobart part works well but the Launceston part fails at the AdvancedFilter saying that "the extract range has a missing or illegal field name"
    2 The second time I run it, it fails at the first AdvancedFilter saying that "the extract range has a missing or illegal field name"
    3 When AdvancedFilter works, it enters the first value twice

    Any ideas? Your help will be much appreciated.
    Last edited by AlbanJ; 10-15-2015 at 08:32 PM.

  2. #2
    Registered User
    Join Date
    10-15-2015
    Location
    Hobart, Tasmania
    MS-Off Ver
    2003
    Posts
    11

    Re: Macro using named ranges and advanced filter only works first time

    Someone suggested it might be because the data starts in AF53. But when I changed it to AF52 no difference. Besides, how would Excel know if AF53 was data or not?

  3. #3
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,637

    Re: Macro using named ranges and advanced filter only works first time

    will need to look at how your data is setup
    advanced filters works on headers

    also you use selection but i don't see no selecting?
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  4. #4
    Registered User
    Join Date
    10-15-2015
    Location
    Hobart, Tasmania
    MS-Off Ver
    2003
    Posts
    11

    Re: Macro using named ranges and advanced filter only works first time

    Hi Humdingaling, Thanks for replying. Does this screenshot have enough info? Or should I attach a copy of the file?

    Capture.JPG

  5. #5
    Registered User
    Join Date
    10-15-2015
    Location
    Hobart, Tasmania
    MS-Off Ver
    2003
    Posts
    11

    Re: Macro using named ranges and advanced filter only works first time

    Hi Humdingaling, I replied to the general tread before I realised I could reply to you directly...

  6. #6
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,637

    Re: Macro using named ranges and advanced filter only works first time

    usually pictures arent the best way for you to get help your better off supplying a sample file

    anyway what i can draw from your picture and your code.... you dont have any CriteriaRange?

    see here if you dont know what im talking about when i say criteria range
    https://msdn.microsoft.com/en-us/lib.../ff841242.aspx

    so you arent using advanced filter to filter anything but rather copy information?
    if that is the case why do you even need advanced filter?

  7. #7
    Registered User
    Join Date
    10-15-2015
    Location
    Hobart, Tasmania
    MS-Off Ver
    2003
    Posts
    11

    Re: Macro using named ranges and advanced filter only works first time

    Sorry, first time I've ever used an Excel forum, so still working out how best to ask things...

    I was using advanced filter to return a set of unique values from a longer list. I'll try to attach a sample spreadsheet example.

    (The attachment is taking forever to load. Don't know what the solution to that is...)

    The list might look like this:
    ALLR
    ALLR
    ALLR
    CARA
    CARA
    CARA
    CARA
    CARA
    CORS
    CORS
    CORS
    CORS
    CORS
    GEAD
    GEAD
    GEAD
    GEAD
    GEAD
    KOST
    KOST
    MCGC
    MCGC

    And what I want to extract is this:
    ALLR
    CARA
    CORS
    KOST
    MCGC

  8. #8
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,637

    Re: Macro using named ranges and advanced filter only works first time

    oh alright i see what your doing now
    i dont see where RG2,3 & 5 get used but that wouldnt stop the code from working

    i replicated what your doing in the small scale and it is doing what the code intends
    however i manhandled the data to be specific to the case
    it may be the case your file has different data layout which is causing the issue

    ive attached the small scale version
    maybe you can tell me what is different
    Attached Files Attached Files

  9. #9
    Forum Contributor tax112's Avatar
    Join Date
    02-28-2013
    Location
    Thailand
    MS-Off Ver
    Excel 2003 | 2010
    Posts
    385

    Re: Macro using named ranges and advanced filter only works first time

    Quote Originally Posted by AlbanJ View Post
    Sorry, first time I've ever used an Excel forum, so still working out how best to ask things...

    I was using advanced filter to return a set of unique values from a longer list. I'll try to attach a sample spreadsheet example.

    (The attachment is taking forever to load. Don't know what the solution to that is...)

    The list might look like this:
    ALLR
    ALLR
    ALLR
    CARA
    CARA
    CARA
    CARA
    CARA
    CORS
    CORS
    CORS
    CORS
    CORS
    GEAD
    GEAD
    GEAD
    GEAD
    GEAD
    KOST
    KOST
    MCGC
    MCGC

    And what I want to extract is this:
    ALLR
    CARA
    CORS
    KOST
    MCGC
    You do the following:
    - Suppose your string in column A, starting in cell A1.
    - Input B1 = 1, pull down, equivalent to string column A.
    - In Data \ Consolidate
    Function: Sum
    Reference: $ A $ 1: $ B $ 22
    - Tick the Left and Left column
    - Click OK
    Work completed

  10. #10
    Forum Contributor tax112's Avatar
    Join Date
    02-28-2013
    Location
    Thailand
    MS-Off Ver
    Excel 2003 | 2010
    Posts
    385

    Re: Macro using named ranges and advanced filter only works first time

    You see Attachment
    Attached Files Attached Files

  11. #11
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016 (preparing to update to 2021)
    Posts
    7,544

    Re: Macro using named ranges and advanced filter only works first time

    Very simple, the proble is that you use the xlDown and then it alwasy stops at the last filled cell
    LAUNCESTON has an empty row.

    Change the code as follows and it will work:

    Please Login or Register  to view this content.
    ---
    Hans
    "IT" Always crosses your path!
    May the (vba) code be with you... if it isn't; start debugging!
    If you like my answer, Click the * below to say thank-you

  12. #12
    Registered User
    Join Date
    10-15-2015
    Location
    Hobart, Tasmania
    MS-Off Ver
    2003
    Posts
    11

    Re: Macro using named ranges and advanced filter only works first time

    Thank you so much!!!

    That works beautifully. As you say, its very simple ... when you know how.

    Really appreciate your help.

  13. #13
    Registered User
    Join Date
    10-15-2015
    Location
    Hobart, Tasmania
    MS-Off Ver
    2003
    Posts
    11

    Re: Macro using named ranges and advanced filter only works first time

    Thanks very much, this is an approach I never knew about and it will be very useful.

  14. #14
    Registered User
    Join Date
    10-15-2015
    Location
    Hobart, Tasmania
    MS-Off Ver
    2003
    Posts
    11

    Re: Macro using named ranges and advanced filter only works first time

    I couldn't see much different either. Keebellah worked out the problem, so all good now. Thanks very much for working on this, I guess we all learn from how it works.

  15. #15
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016 (preparing to update to 2021)
    Posts
    7,544

    Re: Macro using named ranges and advanced filter only works first time

    You could have doen it yourself.

    Go the first row of the LAUNCESTON list, press <End> and then the down arrow, the cursor stops at the last filled row and that is what you do with the macro.
    The other way is go to the last row in that same column, with excel 2007 or newer that's row 1.420.000 something
    select that last cell in nthe same column, press < End> and the Up Arrow and it stops at the first filled row, that is what you need.
    Never too old to learn
    I'm learning everyday and I'm old, but not too old.

  16. #16
    Registered User
    Join Date
    10-15-2015
    Location
    Hobart, Tasmania
    MS-Off Ver
    2003
    Posts
    11

    Re: Macro using named ranges and advanced filter only works first time

    I like that last comment, I too am old, but not too old to learn every day.

  17. #17
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016 (preparing to update to 2021)
    Posts
    7,544

    Re: Macro using named ranges and advanced filter only works first time

    @Albaj: Thanks But it's true. you'r never too old to learn, the moment you get that feeling it's time to order six planks

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Advanced Filter Macro does not work, running manually works fine
    By jamiemc in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-09-2013, 07:22 PM
  2. [SOLVED] VBA Filter Using Named Ranges as Criteria
    By Mediterranean in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 04-29-2013, 04:00 PM
  3. Advanced Filter not working with VBA, but works manually.
    By sundar2182 in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 12-31-2012, 06:00 AM
  4. [SOLVED] Run-time error 1004 when running Advanced Filter for Unique Values in macro
    By jacobadger in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-17-2012, 02:17 PM
  5. Advanced Filter works manually but not in VBA?
    By rob0r in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-29-2009, 08:16 AM
  6. Macro for filter on protected workbook that works for all sheets, no matter what sheets are named?
    By StargateFanFromWork in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-26-2006, 02:35 PM
  7. Advanced filter: criteria in non-adjacent ranges?
    By count in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-11-2005, 10:05 AM
  8. [SOLVED] Advanced filter in VBA - criteria in non-adjacent ranges
    By count in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-11-2005, 09:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1