Hide row if certain cells on the row doesn't contain certain value

[user_name]

I've looked around these forums and done some searching on Google but didn't find exactly what i was looking for so i hope you guys can help me out!

Ok, So i have an excel sheet in which i need to do the following:

I have a dropdown menu with some 300 values in cell A1 from which users can select a value. (e.g. "Test" "Test2" "Test3" etc.)

There are 5040 rows in the sheet and the value chosen from the dropdown menu can occur in 6 cells per row. (Columns K to P)

I need a macro that will check these 6 cells per Row for the value chosen from the dropdown menu in A1.

If any of these 6 cells per row doesn't contain the value selected from the dropdown menu in A1 i want to hide the row that doesn't contain that value.

Then i would also like to have a "reset" button in A2 which you can press to unhide the rows again.

I really hope you guys can help me out.

Thanks,
Bas

 

[user_name]

If my question isn't clear please let me now and i'll explain a little further.
I can also supply the excel file if you guys like.

Thanks in advance!

 

[Peter_SSs]

Welcome to the MrExcel board!

If any of these 6 cells per row doesn't contain the value selected from the dropdown menu in A1 i want to hide the row that doesn't contain that value.

That would mean that you want to show only rows that contain the A1 value in all 6 columns.

Then i would also like to have a "reset" button in A2 which you can press to unhide the rows again.

Assuming your headings are in row 1, this could be a problem because row 2 (containing cell A2) could be hidden as a result of the condition above. That would make it difficult to access the 'reset' button.

What I have done instead is make it so that you can double-click cell B1 and it will show all rows again.

To implement ..

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.

Post back with more details if I haven't interpreted correctly.

Test in a copy of your workbook.

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_BeforeDoubleClick(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range, Cancel <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>)<br>    <SPAN style="color:#00007F">If</SPAN> Target.Address(0, 0) = "B1" <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br>        ActiveSheet.ShowAllData<br>        <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#00007F">Dim</SPAN> v<br>    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Intersect(Target, Range("A1")) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        v = Range("A1").Value<br>        <SPAN style="color:#00007F">With</SPAN> Intersect(ActiveSheet.UsedRange, Columns("K:P"))<br>            <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> .Columns.Count<br>                .AutoFilter Field:=i, Criteria1:=v<br>            <SPAN style="color:#00007F">Next</SPAN> i<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[user_name]

Wow, Thanks for the quik reply!

"That would mean that you want to show only rows that contain the A1 value in all 6 columns."

The A1 value is always just in 1 of the columns and never in all 6 of them.
So the value might be only in column K or sometimes only N.

Assuming your headings are in row 1, this could be a problem because row 2 (containing cell A2) could be hidden as a result of the condition above. That would make it difficult to access the 'reset' button.

What I have done instead is make it so that you can double-click cell B1 and it will show all rows again.

And indeed I meant B1 instead of A2. Sorry for the misunderstanding!

 

[Peter_SSs]

The A1 value is always just in 1 of the columns and never in all 6 of them.
So the value might be only in column K or sometimes only N.

So what did you mean by the following and what rows do you want to hide?

If any of these 6 cells per row doesn't contain the value selected from the dropdown menu in A1 i want to hide the row that doesn't contain that value.s

Do you mean that if any of the cells contain the A1 value you want to show that row?

 

[user_name]

Thanks again for the reply!

Yes thats what i mean.
So if any of the cells in columns K to P contain the A1 value i want to show that row.

I think the macro works fine but now hides all rows because none of the rows contain the A1 value in all six columns.

 

[Peter_SSs]

Try this instead. It assumes column Z is available to use as ahelper column. Adjust the SpareCol line of code if this is not the case.

Keep the double click code as before but replace the Worksheet_Change code with this.

<font face=Courier New><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#00007F">Dim</SPAN> v<br>    <SPAN style="color:#00007F">Dim</SPAN> rCrit <SPAN style="color:#00007F">As</SPAN> Range<br>    <br>    <SPAN style="color:#00007F">Const</SPAN> SpareCol <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "Z"      <SPAN style="color:#007F00">'<- Any spare column</SPAN><br>    <br>    <SPAN style="color:#00007F">Const</SPAN> Frmla <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "=COUNTIF(K2:P2,""#"")"<br>    <br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Intersect(Target, Range("A1")) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> rCrit = Range(SpareCol & 1).Resize(2)<br>        v = Range("A1").Value<br>        rCrit.Cells(2).Formula = Replace(Frmla, "#", v, 1, -1, 1)<br>        <SPAN style="color:#00007F">With</SPAN> Intersect(ActiveSheet.UsedRange, Columns("K:P"))<br>            .AdvancedFilter Action:=xlFilterInPlace, _<br>                CriteriaRange:=rCrit, Unique:=<SPAN style="color:#00007F">False</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        rCrit.ClearContents<br>        Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br></FONT>

 

[user_name]

Wow Thanks Peter that works like a charm!

Thank you very much for your time and help!
You've just made my day

 

[user_name]

Sorry Peter but i had just 1 quick question if you dont mind!

I wanted to alter the file a little bit so that the headers are in row 2.

How do i alter the macro to have it start looking for the values starting at row 3 instead of row 2?
Because now it also checks row 2 for the A1 value and doesn't find it so it hides the row.

The top 2 rows are frozen by the way i don't know if that matters.

Thanks in advance.

 

[Peter_SSs]

Assuming the Data Validation and double-click are still in row 1, try changing the couple of lines with red text below.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim v
    Dim rCrit As Range
    
    Const SpareCol As String = "Z"      '<- Any spare column
    
    Const Frmla As String = "=COUNTIF(K3:P3,""#"")"
    
    If Not Intersect(Target, Range("A1")) Is Nothing Then
        Application.ScreenUpdating = False
        Set rCrit = Range(SpareCol & 1).Resize(2)
        v = Range("A1").Value
        rCrit.Cells(2).Formula = Replace(Frmla, "#", v, 1, -1, 1)
        With Intersect(ActiveSheet.UsedRange, Columns("K:P")).Offset(1)
            .AdvancedFilter Action:=xlFilterInPlace, _
                CriteriaRange:=rCrit, Unique:=False
        End With
        rCrit.ClearContents
        Application.ScreenUpdating = True
    End If
End Sub