PDA

View Full Version : Solved: VBA to select Filter Criteria



JimS
03-20-2012, 08:56 AM
I have the following code that I use to select the criteria of a Filter.
It references a Named Cell ("max_1") that has a =Max(column) formula in it to determine the latest Date (which happens to be 03/24/2012 in my dataset) but for some reason the code is selecting all of the month March 2012 when it does the filtering.

Any ideas why it is selecting all of March and not just 03/24/2012, which is what the value is in the Named Cell max_1?

Thanks for any help...

JimS



Sub Del_Max()
Dim f As Date
f = Range("max_1").Value
Worksheets("Input Data").Select

ActiveSheet.Range("$A$2:$J$2").AutoFilter Field:=4, Operator:= _
xlFilterValues, Criteria2:=Array(1, f)

Application.DisplayAlerts = False
ActiveSheet.UsedRange.Offset(1, 0).Resize(ActiveSheet.UsedRange.Rows.Count - 1).Rows.Delete
Application.DisplayAlerts = True

ActiveSheet.Range("Data").AutoFilter Field:=4

Range("A1").Select

End Sub

p45cal
03-20-2012, 09:45 AM
long shot; try:
f = clng(Range("max_1").Value)

JimS
03-20-2012, 09:50 AM
Thanks for looking at this.

I just tried the f = CLng(Range("max_1").Value) but it still filtered on all the March entries.

JimS
03-20-2012, 04:22 PM
Looks like this fixed it:

Criteria2:=Array(2, f)

Thanks...

JimS