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
    24,006
    Post Thanks / Like
    Mentioned
    408 Post(s)
    Tagged
    43 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 365 on Win 10

  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
    24,006
    Post Thanks / Like
    Mentioned
    408 Post(s)
    Tagged
    43 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 365 on Win 10

  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
    24,006
    Post Thanks / Like
    Mentioned
    408 Post(s)
    Tagged
    43 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 365 on Win 10

  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
    24,006
    Post Thanks / Like
    Mentioned
    408 Post(s)
    Tagged
    43 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 365 on Win 10

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
  •