Help with creating VBA to filter validation list

Hi, I'm new here and found my way through a Google search.

I know very little about programming using VBA in Excel. I am currently using Excel 2007 at home and work. I am working on a spreadsheet but I'm trying to automatically filter information based on the input in the validation list.

I have attached an Excel file that I am working on. I was able to search and find a similar issue/resolution to my issue. Found here:
http://excel.bigresource.com/Track/excel-5DHphNL2/

Unfortunately, I can't figure out how to make it work for my particular application. I have validation lists next to the cells that are highlighted in yellow.

I would like for the "Joist Size" list to hide certain rows in 13-101 based on the matching formula result down column B.

Ex:
If I choose 8 in the validation list next to "Joist Size", then a formula next to the 8" joists will display 8. I would like the macro to hide any rows from 13-101 that do not contain 8. Of course, All would display all values and other numbers would filter my rows.

"LL Deflection Ratio" & "Spacing" would hide columns based on the choice in the list.

Any help would be greatly appreciated!

TIA

Evan

Try this code in the worksheet module.

There are some changes you need to make to the worksheet.
The values in the Joist Size list are all numbers formatted as text. Always use General Format or a specific number format for numbers - but never use Text format.

A helper column is needed (can be hidden), to designate rows relative to joist size. I've chosen column-A. you can use another column, but must revise the code if so.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim c As Range, bottomrow As Long
    
    bottomrow = Cells(Rows.Count, "A").End(xlUp).Row
    
    If Target.Cells.Count > 1 Then Exit Sub
    
    Application.ScreenUpdating = False
    
    With Me
        If Not Intersect(Target, .Range("D6")) Is Nothing Then
            Select Case Target.Value
                Case "All"
                    .Cells.Rows.Hidden = False
                Case Is <> "All"
                    For Each c In .Range("A13", Range("A" & bottomrow))
                        c.EntireRow.Hidden = c.Value <> Target.Value
                    Next c
            End Select
        End If
    End With
    
    Application.ScreenUpdating = True

End Sub

Originally Posted by Palmetto

Try this code in the worksheet module.

There are some changes you need to make to the worksheet.
The values in the Joist Size list are all numbers formatted as text. Always use General Format or a specific number format for numbers - but never use Text format.

A helper column is needed (can be hidden), to designate rows relative to joist size. I've chosen column-A. you can use another column, but must revise the code if so.

Palmetto, thank you very much for your help! I changed the text for my Joists list to General, as you suggested.

I did have a helper column in B but I was using an "IF" formula to display it. I guess, I wasn't thinking simplistic, :-). So I changed the code that you gave me to "B" and I copied your values in Column A to my Column B.

Now, if I want to use the same situation to hide columns, can I just change any of the references of Row to Column? I'm going to try it now to see...wish me luck! lol

I appreciate your help!

I will have to backtrack on my comments on using text formats.

After a bit of testing, regardless of formatting the cells in the look up list, drop down selection cell and helper cells to have the same format, I could not get the rows for 9 1/4 and 11 1/4 to hide/unhide properly.

Convert your drop down cell, list of joist sizes and the helper cells to text. Make sure all cells have the same format and values
Move the helper cells to column-B. Clear cell B12, as this is used in the autofilter.

Rather than looping, filtering is applied which is more efficient.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)


    Dim bottomrow As Long
    
    bottomrow = Cells(Rows.Count, "B").End(xlUp).Row
    
    If Target.Cells.Count > 1 Then Exit Sub
    
    Application.ScreenUpdating = False
    
    With Me
        
        If Not Intersect(Target, .Range("D6")) Is Nothing Then
        
            Select Case Target.Value
                Case "All"
                    .AutoFilterMode = False
                    
                Case Is <> "All"
                    .Range("B12:B" & bottomrow).AutoFilter Field:=1, Criteria1:="=" & Target.Value, visibledropdown:=False
            End Select
        End If
    End With
    
    Application.ScreenUpdating = True

End Sub