VBA to auto filter based on cell value

[nodroj81]

Hi and thanks in advance for any help!

I have a macro that uses auto filter to delete irrelevant rows from a data table. The table is a list of equipment purchases. With each equipment purchase, there is a purchase order number, listed in column A.

Each purchase order number looks like this: "FPyy-####",

so for a purchase made in 2011, the numbers would look like "FP11-9876", "FP11-5432", etc.

and for 2012 the numbers would be "FP12-0001", "FP12-0002", etc...

The macro currently is set to eliminate any orders that don't fall in 2012 (indicated in the VBA with "<>FP12*").

Here is my code:

'Filter for only applicable lines and delete irrelevent data

With ActiveSheet
.AutoFilterMode = False
With Range("A1", Range("L" & Rows.Count).End(xlUp))
.AutoFilter 1, "<>FP12*"
On Error Resume Next
.Offset(1).SpecialCells(12).EntireRow.Delete
End With
.AutoFilterMode = False
End With

What I'm trying to figure out is if there's a way for the user to indicate what year they want to look at, and then the VBA would auto filter based on that choice.

I'm thinking of a set up where the user would input a four digit year value in a given cell, and then the VBA would somehow look at that cell and then apply that as the auto-filter criteria.


Any thoughts or ideas would be greatly appreciated!

 

[Weaver]

Say if X1 was the cell with the year in it

.AutoFilter 1, "<>FP" & format(range("X1") mod 100,"00") & "*"

(untested)

 

[nodroj81]

Thanks Weaver,

the code is triggering an error at the "mod(" portion of the code. Is there a quick fix to this error?

If not, I was able to use your suggestion to solve the problem.

Cell X1 the user can put in the year

Cell X2 has the formula ="<>FP"&TEXT(MOD(X1,100),"00") & "*"

And then the code would read .AutoFilter 1, Sheets("Sheet1").Range("X2")

That did the trick so if it's not a quick fix to your code then don't worry about it.

Thanks again for the quick help!

 

[Weaver]

Sorry, you must have gotten to it before my second edit. Go back and try the new version.

 

[nodroj81]

That worked perfectly - thanks again!