Smart Filter

[jdecastro]

Hi,

I need to have what I call a smart Filter.

If I have 3 Columns and they all have values such as

1 2 3
2 2 3
2 1 3
3 3 3
3 1 1

I want to have a filter in which I only have to filter one column for the number 1, and have a filter for all three columns:

1 2 3
2 1 3
3 1 1

Thanks

JDC

 

[jdecastro]

Hi Hiker,

I'm beginning to use this, and works perfect for filtration, but when I erase the filter, it filters for the empty ones... Is there a code for when the box is empty it simply doesn't filter.

Thanks

jdecastro,


Welcome to the MrExcel forum.


Sample raw data in worksheet Sheet1:

Excel Workbook
	A	B	C	D	E	F
1	Title A	Title B	Title C		Show rows with:
2	1	2	3
3	2	2	3
4	2	1	3
5	3	3	3
6	3	1	1
7
Sheet1

If we enter a 1 in cell F1, we automatically get this:

Excel Workbook
	A	B	C	D	E	F
1	Title A	Title B	Title C		Show rows with:	1
2	1	2	3
4	2	1	3
6	3	1	1
7
Sheet1

If we enter a 2 in cell F1, we automatically get this:

Excel Workbook
	A	B	C	D	E	F
1	Title A	Title B	Title C		Show rows with:	2
2	1	2	3
3	2	2	3
4	2	1	3
7
Sheet1

If we enter a 3 in cell F1, we automatically get this:

Excel Workbook
	A	B	C	D	E	F
1	Title A	Title B	Title C		Show rows with:	3
2	1	2	3
3	2	2	3
4	2	1	3
5	3	3	3
6	3	1	1
7
Sheet1

If we enter a 4 in cell F1, we automatically get this:

Excel Workbook
	A	B	C	D	E	F
1	Title A	Title B	Title C		Show rows with:	4
7
Sheet1

If we delete what is in cell F1, we automatically get this:

Excel Workbook
	A	B	C	D	E	F
1	Title A	Title B	Title C		Show rows with:
2	1	2	3
3	2	2	3
4	2	1	3
5	3	3	3
6	3	1	1
7
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Adding the Macro
1. Copy the below macro, by highlighting the macro code and pressing the keys CTRL + C
2. Open your workbook
3. Right click the sheet tab you want the code in (in this example, Sheet1), and click on View Code. Paste the below code there (on the right pane) by pressing the keys CTRL + V
4. Press the keys ALT + Q to exit the Editor, and return to Excel

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
' hiker95, 10/23/2010, ME503884
If Intersect(Target, Range("F1")) Is Nothing Then
  Exit Sub
Else
  Call Test
End If
End Sub

Adding the Macro
1. Copy the below macro, by highlighting the macro code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel

Option Explicit
Sub Test()
' hiker95, 10/23/2010, ME503884
Dim LR As Long, a As Long
Application.ScreenUpdating = False
With Worksheets("Sheet1")
  .UsedRange.Rows.Hidden = False
  If .Range("F1") = "" Then
    Application.ScreenUpdating = True
    MsgBox "There is no search data in cell F1 - macro terminated!"
  Else
    LR = .Cells(Rows.Count, 1).End(xlUp).Row
    If LR > 1 Then
      For a = LR To 2 Step -1
        If Application.CountIf(.Range("A" & a & ":C" & a), .Range("F1")) = 0 Then .Rows(a).Hidden = True
      Next a
    Else
      Application.ScreenUpdating = True
      MsgBox "There is no raw data in column A - macro terminated!"
    End If
  End If
End With
End Sub

Then make changes to cell F1 in worksheet Sheet1.

 

[hiker95]

jdecastro,


Sample raw data in worksheet Sheet1:

Excel Workbook
	A	B	C	D	E	F
1	Title A	Title B	Title C		Show rows with:
2	1	2	3
3	2	2	3
4	2	1	3
5	3	3	3
6	3	1	1
7
Sheet1

If we enter a 1 in cell F1, we automatically get this:

Excel Workbook
	A	B	C	D	E	F
1	Title A	Title B	Title C		Show rows with:	1
2	1	2	3
4	2	1	3
6	3	1	1
7
Sheet1

If we delete what is in cell F1, we automatically get this:

Excel Workbook
	A	B	C	D	E	F
1	Title A	Title B	Title C		Show rows with:
2	1	2	3
3	2	2	3
4	2	1	3
5	3	3	3
6	3	1	1
7
Sheet1

If we enter a 4 in cell F1, we automatically get this:

Excel Workbook
	A	B	C	D	E	F
1	Title A	Title B	Title C		Show rows with:	4
7
Sheet1

If we delete what is in cell F1, we automatically get this:

Excel Workbook
	A	B	C	D	E	F
1	Title A	Title B	Title C		Show rows with:
2	1	2	3
3	2	2	3
4	2	1	3
5	3	3	3
6	3	1	1
7
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Select the worksheet Sheet1 in which your the code is to run
3. Right click on the sheet tab and choose View Code, to open the Visual Basic Editor
4. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
5. Press the keys ALT + Q to exit the Editor, and return to Excel

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
' hiker95, 12/04/2010, ME503884
If Intersect(Target, Range("F1")) Is Nothing Or Range("F1") = "" Then
  Me.UsedRange.Rows.Hidden = False
  Exit Sub
Else
  If Target <> "" Then Call Test
End If
End Sub

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel

Option Explicit
Sub Test()
' hiker95, 12/04/2010, ME503884
Dim LR As Long, a As Long
Application.ScreenUpdating = False
With Worksheets("Sheet1")
  .UsedRange.Rows.Hidden = False
  LR = .Cells(Rows.Count, 1).End(xlUp).Row
  If LR > 1 Then
    For a = LR To 2 Step -1
      If Application.CountIf(.Range("A" & a & ":C" & a), .Range("F1")) = 0 Then .Rows(a).Hidden = True
    Next a
  Else
    Application.ScreenUpdating = True
    MsgBox "There is no raw data in column A - macro terminated!"
  End If
Application.ScreenUpdating = True
End With
End Sub

Then make changes to cell F1 in worksheet Sheet1.

 

[Peter_SSs]

Is there a code for when the box is empty it simply doesn't filter.

Yes, the code I already posted in post #4