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

MikeRetyred

New Member
Joined
May 18, 2019
Messages
11
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
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,192
Office Version
365
Platform
Windows
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
 

MikeRetyred

New Member
Joined
May 18, 2019
Messages
11
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
 

MikeRetyred

New Member
Joined
May 18, 2019
Messages
11
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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,192
Office Version
365
Platform
Windows
What error message do you get?
 

MikeRetyred

New Member
Joined
May 18, 2019
Messages
11
Apologies
Error Msg as follows:
Error 1004
The extract name has a missing or invalid field name.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,192
Office Version
365
Platform
Windows
No matter what I try I cannot get the code to fail with that error message, so not sure what to suggest.
 

MikeRetyred

New Member
Joined
May 18, 2019
Messages
11
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
 

MikeRetyred

New Member
Joined
May 18, 2019
Messages
11
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,192
Office Version
365
Platform
Windows
Glad you figured it out & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,099,985
Messages
5,471,815
Members
406,788
Latest member
cmtjack

This Week's Hot Topics

Top