Results 1 to 10 of 10

Thread: How can I use a Range Variable in the Advanced Filter VBA Code

  1. #1
    New Member
    Join Date
    May 2019
    Location
    2 miles inside Scots Border
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Arrow How can I use a Range Variable in the Advanced Filter VBA Code

    Hi,
    My project is to create an Excel Personal Accounts WorkBook to replace MS Money.
    I'm using a Apple Mac Mini with Office 365 - OK with Excel, very new to VBA, so I'm mostly adapting recorded macros. in my 80th year this is challenging - simplicity is key
    Project Summary:
    Sheet 1: "TransTable" - a 'flat' table containing all transactions
    Sheet 2: "Reconcile" - containing transactions selected from Sheet 1 using the Advance Filter to be analysed/reconciled.
    A Count value in Sheet 1 "A1" holds the number of rows / transactions.
    Question:
    How can I create a Variable to use in the Advanced Filter Range.
    My Code so far:
    Sub AdvancedFilterTest()
    '
    ' AdvancedFilterTest Macro
    'I have tried various ways to create a Variable - as below
    'Dim rg As Range

    'Set rg = Worksheets("TransTable").Range("B2").CurrentRegion
    ' but to no avail

    'Advanced Filter Recorded Code

    Sheets("Reconcile").Select
    Application.CutCopyMode = False
    Application.CutCopyMode = False
    Application.CutCopyMode = False
    Sheets("TransTable").Range("B2:M172").AdvancedFilter Action:=xlFilterCopy, _
    CriteriaRange:=Sheets("TransTable").Range("U2:U3"), CopyToRange:=Range( _
    "B2:M2"), Unique:=False
    End Sub

    Many thanks in advance.
    Mike

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    22,769
    Post Thanks / Like
    Mentioned
    381 Post(s)
    Tagged
    40 Thread(s)

    Default Re: How can I use a Range Variable in the Advanced Filter VBA Code

    Hi & welcome to MrExcel.
    How about
    Code:
    Sub MikeRetyred()
       Dim rg As Range
       Set rg = Worksheets("TransTable").Range("B2").CurrentRegion
       
       Sheets("Reconcile").Range("B2").CurrentRegion.ClearContents
       rg.AdvancedFilter xlFilterCopy, Sheets("TransTable").Range("U2:U3"), _
          Sheets("Reconcile").Range("B2"), False
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  3. #3
    New Member
    Join Date
    May 2019
    Location
    2 miles inside Scots Border
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How can I use a Range Variable in the Advanced Filter VBA Code

    Gosh Fluff I'm impressed and most grateful.
    I'll try it out later this evening (after dinner - i cook as well, and not just The Books !)
    MikeRetyred

  4. #4
    New Member
    Join Date
    May 2019
    Location
    2 miles inside Scots Border
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How can I use a Range Variable in the Advanced Filter VBA Code

    Hi Fluff,
    Again, thanks for your swift response.
    I have somehow missed out on transcription - probably a schoolboy error.
    In order to understand your solution I created a Sub AdvancedFilterFluff() as follows, adding back some of the extraneous stuff in an attempt to understand better.:
    Dim rg As Range
    Set rg = Worksheets("TransTable").Range("B2").CurrentRegion

    Sheets("Reconcile").Range("B2").CurrentRegion.ClearContents
    Sheets("TransTable").Select
    rg.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Sheets("TransTable").Range("U2:U3"), _
    CopyToRange:=Sheets("Reconcile").Range("B2"), Unique:=False

    End Sub

    Unfortunately the last section fails error code 1004 (with or without the extraneous stuff).
    Advice would be appreciated.

  5. #5
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    22,769
    Post Thanks / Like
    Mentioned
    381 Post(s)
    Tagged
    40 Thread(s)

    Default Re: How can I use a Range Variable in the Advanced Filter VBA Code

    What error message do you get?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  6. #6
    New Member
    Join Date
    May 2019
    Location
    2 miles inside Scots Border
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How can I use a Range Variable in the Advanced Filter VBA Code

    Apologies
    Error Msg as follows:
    Error 1004
    The extract name has a missing or invalid field name.

  7. #7
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    22,769
    Post Thanks / Like
    Mentioned
    381 Post(s)
    Tagged
    40 Thread(s)

    Default Re: How can I use a Range Variable in the Advanced Filter VBA Code

    No matter what I try I cannot get the code to fail with that error message, so not sure what to suggest.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  8. #8
    New Member
    Join Date
    May 2019
    Location
    2 miles inside Scots Border
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How can I use a Range Variable in the Advanced Filter VBA Code

    I really appreciate all your effort on this one. i'll take a look tomorrow and see if I can find anything perhaps by using another test Workbook with the same sheet names. if I find a solution in the next few days - I'll message you
    Thanks again,
    Mike

  9. #9
    New Member
    Join Date
    May 2019
    Location
    2 miles inside Scots Border
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How can I use a Range Variable in the Advanced Filter VBA Code

    Hi Fluff,
    My error !
    I had an entry in Cell A1, which conflicted with 'Range("B2").CurrentRegion'.
    An overnight - Cell A1 deleted - your code worked straight off.
    It was a schoolboy error.
    Many thanks
    Mike

  10. #10
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    22,769
    Post Thanks / Like
    Mentioned
    381 Post(s)
    Tagged
    40 Thread(s)

    Default Re: How can I use a Range Variable in the Advanced Filter VBA Code

    Glad you figured it out & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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