[SOLVED]Hide Rows and Columns Based on User Input

[thesteve]

Hi all,

I'm running into an issue in trying to hide rows based on the input in one cell and hide columns based on the input in the other. Thus far I have only managed to make hiding the rows work as I intended. The code for that is below. How can I also hide columns K:S if "G15" = 1, L:S if "G15" = 2, ... S:S if "G15" = 9? Sorry if this wasn't the most clear.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target <> Range("G16") Then Exit Sub
Sheets("Sheet1").Rows.Hidden = False
Select Case Range("G16").Value
    Case 1

        Sheets("Sheet1").Rows("24:64").Hidden = True
    
    Case 2
    
        Sheets("Sheet1").Rows("29:64").Hidden = True
        
    Case 3
    
        Sheets("Sheet1").Rows("34:64").Hidden = True
            
    Case 4
    
        Sheets("Sheet1").Rows("39:64").Hidden = True
        
    Case 5
    
        Sheets("Sheet1").Rows("44:64").Hidden = True
        
    Case 6
    
        Sheets("Sheet1").Rows("49:64").Hidden = True
        
    Case 7
    
        Sheets("Sheet1").Rows("54:64").Hidden = True
        
    Case 8
    
        Sheets("Sheet1").Rows("59:64").Hidden = True
        
    Case 9
    
        Sheets("Sheet1").Rows("64:64").Hidden = True
        
End Select
End Sub

[kcarley]

Hi there! I would say that you could easily add a new Select...Case block but that would also need some tweaks to the If...Then logic and simply too many lines of code for my taste. I think this may be easy enough to follow but what's more, it simply works the way you've asked. Oh, by-the-way, this works on the sheet that is active so it can be moved from sheet to sheet or added to a module by itself and called on when needed.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
' Ignore text values
    If Not IsNumeric(Target.Value2) Then Exit Sub
' Only reset Hidden if G15 or G16 are changed
    If Target <> Range("G15") And Target <> Range("G16") Then Exit Sub
    ' Hide Columns
        If Target.Value2 <= 9 And Target.Address = "$G$15" Then
        ' UnHide all columns
            ActiveSheet.Columns.Hidden = False
        ' Hide required columns
            Range(Cells(1, 10 + Target.Value2), Cells(1, 19)).EntireColumn.Hidden = True
        End If
    'Hide Rows
        If Target.Value2 <= 9 And Target.Address = "$G$16" Then
        ' UnHide all rows
            ActiveSheet.Rows.Hidden = False
        ' Hide required rows
            Range(Cells(19 + Target.Value2 * 5, 1), Cells(64, 1)).EntireRow.Hidden = True
        End If
End Sub

Cheers!

[thesteve]

Thanks so much, this works perfectly as I was beginning to get worried I wouldnt ever get it done!

I really appreciate it.

[kcarley]

Very HAPPY to help.

I have to ask a favor, I'd do it for you but I can't; don't forget to change the title in your original post. Just click Pencil (EditPost.gif) at the bottom of post #1 and add [SOLVED] to the beginning.

Also remember that you can click the Star (Reputation.gif) symbol in the bottom left corner of any and all posts that you considered "helpful".

Enjoy!
K