Need help filtering a spreadsheet using a macro based on criteria in a cell

[dieroo]

Hi all, I'm currently working on a spreadsheet and I need to be able to filter it based on state (theres only about 6). I built a different macro for each state that automatically filters like if you were to do it manually. My plan was to chain together a few IF statements in a cell to execute the macro I wanted. In my mind it was going to look something like, "IF(A1=CA,"RunMacroCA",IF(....)). I can't figure out the language I need to get the macro to run, if its even possible. Any help is greatly appreciated!

If this isn't the correct way to go about it, how can I automatically filter based on a given value in a cell?

 

[jim may]

Welcome to the Board...

What version of Excel are you using? and, how have you set up your "filtering process" -- auto-filtering or advanced filtering?

 

[dieroo]

Welcome to the Board...

What version of Excel are you using? and, how have you set up your "filtering process" -- auto-filtering or advanced filtering?

Hi Jim!
I'm running excel 2013 and am not using the advanced filtering option. I simply clicked the dropdown box in the "State" column and unchecked all then reselected the specific state desired for each macro. They have been tested and are certainly functional so far.
Thanks again.

 

[Peter_SSs]

Welcome to the MrExcel board!

1. How does the value (eg "CA") get into cell A1? does the user enter it, or is there a formula in A1? If a formula, what is the formula?

2. Where is the data that is to be filtered?

 

[dieroo]

Welcome to the MrExcel board!

1. How does the value (eg "CA") get into cell A1? does the user enter it, or is there a formula in A1? If a formula, what is the formula?

2. Where is the data that is to be filtered?

1) The value is entered manually by the user. No formula is neccesary because the user will enter specific criteria.

2) The data that is to be filtered is on the same sheet, within the same worksheet only a few cells away.

 

[Peter_SSs]

2) The data that is to be filtered is on the same sheet, within the same worksheet only a few cells away.

That is very non-specific so you will need to modify this code to match the column your states are in (I've used column D). However, your answer to 1. means that you only need one set of code. To implement the code below,


1. Right click the sheet name tab and choose "View Code".

2. Copy and Paste the code below into the main right hand pane that opens at step 1.

3. Close the Visual Basic window & test by entering, changing/deleting a value in A1

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("A1")) Is Nothing Then
    If Range("A1").Value = "" Then
      Range("D1", Range("D" & Rows.Count).End(xlUp)).AutoFilter Field:=1
    Else
      Range("D1", Range("D" & Rows.Count).End(xlUp)).AutoFilter Field:=1, Criteria1:=Range("A1").Value
    End If
  End If
End Sub

 

[jim may]

Sample Data...

Excel 2012
	A	B	C	D
1	Y	<< Enter Criteria Here (Either Y or N)
2
3
4	Amt	Name	Going?	Date
5	123.00	Howard	Y	7/15/2003
6	234.00	Bruce	N	7/28/2003
7	345.00	Ron	N	8/31/2003
8	456.00	Howard	N	8/1/2003
9	567.00	Ron	Y	9/5/2003
10	678.00	Joe	N	6/30/2003
11	347.00	Howard	Y	7/29/2003
12	456.00	Ron	N	9/10/2003
Sheet1

Sub Foo()
Dim Rng As Range
    Set Rng = Range("A4").CurrentRegion
    ActiveSheet.AutoFilterMode = False
    Rng.AutoFilter Field:=3, Criteria1:="=" & ActiveSheet.Range("A1").Value
End Sub