VBA to prompt for criteria of Autofilter

[caseyray]

I've tried searching the site, but apparently I do not know the proper terminology to get an answer to my problem.

I have a data dump spread sheet with row A1 being the header and actual data starting in cell "A2". Column "A" contains my abbreviated department names. I'd like to write a macro that would prompt me for the department I would like to keep and delete all other rows beginning in row "A2" that do not contain my criteria.

Below is my recorded macro that deletes only "BMT" department. I have 10 other departments and only need to report on one department at any time. The way our programmers set up the data dump is all records from our SQL server, they claim not to have time to make this small change and I should do it via autofilter in excel. I would like to add this routine to my formatting macro.

Sub TEST()
'
Range("A1:O1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="<>BMT", Operator:=xlAnd
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.EntireRow.Delete
Selection.AutoFilter
Range("A1").Select
End Sub

Any help will be much appreciated.

 

[mikerickson]

This should do it. You might want further testing to protect against the user misspelling "Acounting Department", etc.

Sub TEST()
'
Dim uiDeptToShow as String

uiDeptToShow = Application.InputBox("Show which Department", type:=2)
If uiDeptToShow = "False" Then Exit Sub:Rem Cancel pressed

Range("A1:O1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="=" & uiDeptToShow, Operator:=xlAnd
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.EntireRow.Delete
Selection.AutoFilter
Range("A1").Select
End Sub

 

[caseyray]

mikerickson, brilliant, thank you very much. I did have to make one slight change to get it to work, but you are saving me much time in the future.

I changed:

Selection.AutoFilter Field:=1, Criteria1:="=" & uiDeptToShow, Operator:=xlAnd

to:

Selection.AutoFilter Field:=1, Criteria1:="<>" & uiDeptToShow, Operator:=xlAnd


Have a nice weekend.

Casey

 

[Datsmart]

I would suggest looking at Advanced Filtering to do what you are after.
Good example of Advanced Filter steps here:
http://www.contextures.com/xladvfilter01.html

AdvancedFilter Demo link:
http://www.contextures.com/xlVideos04.html

Another good link with sample VBA code here:
http://www.meadinkent.co.uk/xlfilter.htm

Advanced Filter allows you to preset filter options.
It also allows you to filter by criteria in ways standard autofilter can not do. You can choose an export location in the dialog box or in VBA code.
Once you setup the code and criteria, you can use a macro button to copy specific data from your main sheet to a "Report" sheet. The original data remains unchanged and the data "Filtered" to another page can be used as desired.

 

[caseyray]

DATSMART,

Thank you for the links. I will view them this weekend.

Casey