VBA Code to apply filter based on user inputboxes for column alpha-numeric and date

kkbakic

New Member
Joined
Apr 22, 2018
Messages
10
I would like to write vba code that asks the user "which column" they want to filter; and then "what date" they want to filter that column by (ie any date after mm/dd/yyyy)

Something like:

Sub FilterByInput()

Dim iPutFound As Range
Dim mycol As String​
Dim mydate as Variant
mycol = Application.InputBox("Enter alpha-numeric of Column to be filtered -ie. AP3)
mydate = Application.InputBox("Enter date since last update was ran- mm/dd/yyyy")​
Application.ScreenUpdating = False​
Range(mycol).Select​
ActiveSheet.Range ("$A$1:$DK$11000).Autofilter Field = Selection.Column, Criteria1:=Array(mydate), Operator=xlFilterValues​
'The code will resume with copying and pasting the filtered results to another sheet, etc
End sub​
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Does this get you any closer to what you want...

Code:
Sub FilterByInput()


    Dim iPutFound As Range
    Dim mycol As String
    Dim mydate As String
    
    mycol = Application.InputBox("Enter alpha-numeric of Column to be filtered -ie. AP3", Type:=2)
    mycol = Range(mycol & 1).Column
    mydate = Application.InputBox("Enter date since last update was ran- mm/dd/yyyy", Type:=2)
    Application.ScreenUpdating = False
    ActiveSheet.Range("$A$1:$DK$11000").AutoFilter Field:=mycol, Criteria1:=">" & mydate, Operator:=xlFilterValues
    
    'The code will resume with copying and pasting the filtered results to another sheet, etc


End Sub
 
Upvote 0
Thanks igold. I think it gets me closer, but I get an "Autofilter method of Range class failed" run-time error when I get to the following line:
ActiveSheet.Range("$A$1:$DK$11000").AutoFilter Field:=mycol, Criteria1:=">" & mydate, Operator:=xlFilterValues

I tried changing it to:
 
Upvote 0
Thanks igold. I think it gets me closer, but I get an "Autofilter method of Range class failed" run-time error when I get to the following line:
ActiveSheet.Range("$A$1:$DK$11000").AutoFilter Field:=mycol, Criteria1:=">" & mydate, Operator:=xlFilterValues

I tried changing it to:
ActiveSheet.Range("A3:DK3").AutoFilter Field:=mycol, Criteria1:=">" & mydate, Operator:=xlFilterValues

because my header row is on row 3, but that didn't work. Also tried turning the filters off before running the code, but that didn't work either.
 
Upvote 0
Running this code with the sheet that you want filtered as the activesheet, does this work. I filled a sheet with gibberish, except for Column AF which I put dates in, with Row 3 as the header row and this code tested fine. The only caveat I found was that your dates on the activesheet must be in the format of "mm/dd/yyyy".

Code:
Sub FilterByInput()


    Dim iPutFound As Range
    Dim mycol As String
    Dim mydate As String
    
    mycol = Application.InputBox("Enter alpha-numeric of Column to be filtered -ie. AP3", Type:=2)
    mycol = Range(mycol & 1).Column
    mydate = Application.InputBox("Enter date since last update was ran- mm/dd/yyyy", Type:=2)
    Application.ScreenUpdating = False
    ActiveSheet.Range("$A$3:$DK$11000").AutoFilter Field:=mycol, Criteria1:=">" & mydate, Operator:=xlFilterValues
    
    'The code will resume with copying and pasting the filtered results to another sheet, etc


End Sub
 
Upvote 0
You're right igold. It works fine when I make a "test" worksheet. There must be some other conflict. I'll keep working at it, but it's good to know that it should work! Thanks so much for your help. You rock!
 
Upvote 0
You're welcome. I was happy to help. Thanks for the feedback!
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,171
Members
453,021
Latest member
Justyna P

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
Back
Top