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
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
No matter what I try I cannot get the code to fail with that error message, so not sure what to suggest.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Glad you figured it out & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top