Second Advanced Filter not Working

martipe1

Board Regular
Joined
Mar 16, 2007
Messages
69
I recorded a macro for advanced filtering, the first filter is to extract data that meets the criteria(value >0), and the second one using one column (dates) of the first extracted data to get unique data, but this last part is not working as it returns blanks.

This is the code recorded

Sub Macro6()
'
' Macro6 Macro
'

'
Range("A1:G5").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
"I1:O2"), CopyToRange:=Range("Q1"), Unique:=False
Columns("S:S").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("Z1" _
), Unique:=True


End Sub

What's wrong?

Thanks
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,993
Office Version
  1. 2007
Platform
  1. Windows
The macro works for me.
Put your test data sample here.
Use XL2BB tool minisheet.
 

martipe1

Board Regular
Joined
Mar 16, 2007
Messages
69
Thank you very much for your feedback, below you will find my minisheet

Book1
ABCDEFGHIJKLMNO
1Register DateResponsibleDeposit DateAmountBalanceCounterIdentifierRegister DateResponsibleDeposit DateAmountBalanceCounterIdentifier
209/17/2021 13:19:01Eduardo Martinez17/09/20212500250011*44456*2500>0
309/17/2021 13:29:00Eduardo Martinez15/09/20211700170022*44454*1700
409/17/2021 13:29:17Eduardo Martinez16/09/2021300033*44455*300
509/17/2021 13:29:47Eduardo Martinez15/09/20211500150044*44454*1500
Sheet1
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
66,064
Office Version
  1. 365
Platform
  1. Windows
You need to clear the criteria range, try
VBA Code:
Sub Macro6()
'
' Macro6 Macro
'

'
Range("A1:G5").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
"I1:O2"), CopyToRange:=Range("Q1"), Unique:=False
Columns("S:S").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:="", CopyToRange:=Range("Z1" _
), Unique:=True


End Sub
 
Solution

martipe1

Board Regular
Joined
Mar 16, 2007
Messages
69
You need to clear the criteria range, try
VBA Code:
Sub Macro6()
'
' Macro6 Macro
'

'
Range("A1:G5").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
"I1:O2"), CopyToRange:=Range("Q1"), Unique:=False
Columns("S:S").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:="", CopyToRange:=Range("Z1" _
), Unique:=True


End Sub
Thank you!!
That was the issue.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
66,064
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Forum statistics

Threads
1,148,364
Messages
5,746,276
Members
424,003
Latest member
paaskanama

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
Top