How to replace a criteria in AutoFilter by a variable?

Pana1

New Member
Joined
Jul 28, 2010
Messages
11
Hi!

I have this code:


Sub Macro4()
'
' Macro4 Macro
'

'
Range("A1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$E$25").AutoFilter Field:=1, Criteria1:="16/08/2008", _
Operator:=xlAnd
End Sub


Instead of having to insert "16/08/2008" as Criteria1, I would like to have a variable in there corresponding to the maximum value of a column containing dates.

Could anyone help? That would be extremely helpful and very much appreciated...

In this case the maximum would correspond to the most recent date in the DATE column....

DATE SELLER ITEM QUANTITY PRICE
01.01.2008 John SHOES 10 100
02.01.2008 John TROWSERS 20 110
03.01.2008 John SHIRTS 10 80
04.01.2008 John JEANS 25 90
05.01.2008 John SOCKS 20 95
06.01.2008 John SHOES 32 75
07.01.2008 John TROWSERS 45 65
08.01.2008 John SHIRTS 11 95
09.01.2008 John JEANS 12 12
10.01.2008 John SOCKS 14 13
11.01.2008 John SOCKS 10 100
12.01.2008 John SHOES 20 110
12.01.2008 John TROWSERS 10 80
12.01.2008 John SHIRTS 25 90
13.01.2008 John JEANS 20 95
14.01.2008 John SOCKS 32 75
15.01.2008 John SOCKS 45 65
16.01.2008 John SHOES 11 95
16.01.2008 John TROWSERS 12 12
16.01.2008 John SHIRTS 14 13

Thank you again...
 

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,872
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
you maybe need something like:

Code:
Dim rngToCopy As Range
    ...
        Set rngToCopy = Nothing
        Set rngToCopy = Activesheet.Range("A3:D40").SpecialCells(xlCellTypeVisible)
        If Not rngToCopy Is Nothing Then rngToCopy.Copy 
....
End With

Sorry but my day is almost over - maybe continue tomorrow or some guru may do better than me :)
 

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.

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,872
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
In fact, I think the variable is not working at all... If there are sales for two different days in the Sheet 2 (TRANSACTIONS), then it filters out all dates and returns only blank cells... It's as if it did the opposite of what it should do.

i thought it did work at some point ??
 

Pana1

New Member
Joined
Jul 28, 2010
Messages
11
No Worries!

Could you just tell me in which sheet I should paste that code? The one with the TRANSACTIONS or the one with REGISTER

Thanks a lot and see you soon!


ps.: Yes it did work indeed... I'll investigate further on that
 

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,872
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
it's best i think if you put it in a module or in "ThisWorkbook"
 

Forum statistics

Threads
1,181,638
Messages
5,931,152
Members
436,777
Latest member
robk123

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