VBA to prompt for criteria of Autofilter

caseyray

New Member
Joined
Mar 2, 2007
Messages
23
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.
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,916
This should do it. You might want further testing to protect against the user misspelling "Acounting Department", etc.
Code:
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

New Member
Joined
Mar 2, 2007
Messages
23
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

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,910
Messages
5,598,803
Members
414,260
Latest member
joishe

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
Top