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

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
39,164
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
39,164
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
39,164
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
39,164
Office Version
365
Platform
Windows
Glad you figured it out & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,096,458
Messages
5,450,574
Members
405,619
Latest member
mjh237

This Week's Hot Topics

Top