Macro filter table on dates before cell value

[karldugan]

Hi all,

I am trying to get a macro to filter a table for anything before a value in cell C2, but when the macro runs it doesn't filter on anything, even though there are dates before the cell value.

Any help greatly appreciated.
Karl.

Sub Filter_Date()
'
' Macro4 Macro
'

'
ActiveSheet.ListObjects("Matter_List").Range.AutoFilter Field:=9, Criteria1 _
:="<" & Cells(2, 3), Operator:=xlAnd
Range("Matter_List[Bill Month 1]").Select
Selection.ClearContents
ActiveSheet.ListObjects("Matter_List").Range.AutoFilter Field:=9
End Sub

 

[Joe4]

Are you sure that your values in cell C2 and column 9 are all valid numeric entries, and not text entries?
Note it is easy to check with the ISNUMBER function, i.e. if
=ISNUMBER(C2)
returns FALSE, you have a text entry and not a date one.
Likewise, test out some values in column 9 with the function to make sure that you have valid dates there too.

Also, what is your code supposed to be doing?
Note that it will NOT delete rows of values meeting your criteria, it just clears out the values in the "Bill Month 1" column, but will leave the rows there.

 

[karldugan]

Hi Joe4,

Thanks for responding - yes everything is showing as a number.

Re clearing, thats what I want it to do - The lines are for orders that should have been invoiced but weren't, so I want to clear anything that isdated before the reporting month (which is the value in C2). The lines need to remain so the user is prompted to enter a new date.

Thanks,
Karl.

 

[Joe4]

What happens if you step through your code one line at a time using the F8 key?
You can can to the Macros -> View Macros menu, highlight your Macro name, and click the "Step Into" button".
Then press the F8 key to move one line at a time to see what is happening on your worksheet.
Specifically, you want to see what happens when the first filter is applied and before anything is cleared.
Does it look like it is filtering the correct rows?

If so, continue on by hitting F8 and moving after the "ClearContents" line. Is anything being cleared?

This method of debugging is very good, as you can see exactly what is happening with each step of your code.
Many times, the problem point becomes obvious when you can see exactly what is happening.