Hide / Unhide Rows and Columns based on selected cell value (drop-down)

[iamgujju]

Evening all,

First of all i am new here and learning new things with VBA code.

I have been to other Excel/VBA forum too and someone have helped me with code....which you can see below....

i have got data range C6 to CA1000.... columns and rows contain data....and i have created drop-down box at cell B3.

if i select "Monday" from drop-down....only those rows and columns required visible; which contain "Monday" otherwise hide the rest.

the below mentioned code does work but it hides those data which contain "Monday".... could i request for some experts help please.

for your reference i have attached my test2 file too; please use test2 file.

your help would be much appreciated; thanks in advance.Test1.xlsx

Private Sub Worksheet_Change(ByVal Target As Range)
 
 
Dim dataRange As Range
Dim col As Range, row As Range
 
Set dataRange = Range("c6:ca1000")
 
If Not Intersect(Target, Range("B3")) Is Nothing And Target.Count = 1 Then
 
Application.ScreenUpdating = False
 
dataRange.Columns.Hidden = False
dataRange.Rows.Hidden = False
 
If Target.Value <> "ALL" Then
For Each col In dataRange.Columns
If WorksheetFunction.CountIf(col, Target.Value) > 0 Then col.Hidden = True
Next
For Each row In dataRange.Rows
If WorksheetFunction.CountIf(row, Target.Value) > 0 Then row.Hidden = True
Next
 
End If
 
Application.ScreenUpdating = True
 
End If
 
End Sub

[Bob Phillips]

In what way does it not work? The code seems to look for the B3 value in each column, if found it hides that column. Then it does the same for each row. This seems odd to me, all of the Mondays will be hidden in columns before checking the rows, so it is redundant.

[iamgujju]

Thank you very much for your prompt reply; please accept an apology for the confusion.

please find attached Test3.xlsx....i have highlighted MONDAY in RED now.

so if i select MONDAY from drop down box (B3) - row(s) 6,22 & 23 and column C stay visible and the rest needs to be hidden.... i hope this time i may not confuse you. thanks again.

In what way does it not work? The code seems to look for the B3 value in each column, if found it hides that column. Then it does the same for each row. This seems odd to me, all of the Mondays will be hidden in columns before checking the rows, so it is redundant.

[Bob Phillips]

Your code is back to front, it hides what you want to remain visible. Also, in your dropdown list, it says 'Moday' not 'Monday'.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim dataRange As Range
Dim col As Range, row As Range
 
    Set dataRange = Me.Range("C6:CA1000")
     
    If Not Intersect(Target, Me.Range("B3")) Is Nothing And Target.Count = 1 Then
     
        Application.ScreenUpdating = False
         
        dataRange.Columns.Hidden = False
        dataRange.Rows.Hidden = False
         
        If Target.Value <> "ALL" Then
        
            For Each col In dataRange.Columns
            
                col.Hidden = Application.CountIf(col, Target.Value) = 0
            Next
            
            For Each row In dataRange.Rows
            
                row.Hidden = Application.CountIf(row, Target.Value) = 0
            Next
        End If
         
        Application.ScreenUpdating = True
    End If
End Sub

[iamgujju]

i can amend Monday - is there anything you can help me with the code - so it work like how i want it please?

honestly i am well desperate for this to be sorted - any help would be much appreciated.


[QUOTE=xld;391914]Your code is back to front, it hides what you want to remain visible. Also, in your dropdown list, it says 'Moday' not 'Monday'.

[iamgujju]

how thick i am... that code doing how i want it but now only issue is when select ALL - its hidding everything; sorry to be a pain but if select ALL - it should show everything back as normal please.

[iamgujju]

Thanks a lot for your help - it does work for Monday to Saturday now but only ALL is hiding everything instead of visible everything... could you please help; thanks again.

[iamgujju]

[QUOTE=xld;391914]Your code is back to front, it hides what you want to remain visible. Also, in your dropdown list, it says 'Moday' not 'Monday'.


sorry to bother you again - would you please help me with when i select "ALL" its hiding everything in range - i need all rows and columns VISIBLE when select ALL please. really appreciate for your help.

[Bob Phillips]

That is because the value in the drop-down is 'All', your code tests for 'ALL'. This should cater for it

Private Sub Worksheet_Change(ByVal Target As Range)
Dim dataRange As Range
Dim col As Range, row As Range
 
    Set dataRange = Me.Range("C6:CA1000")
     
    If Not Intersect(Target, Me.Range("B3")) Is Nothing And Target.Count = 1 Then
     
        Application.ScreenUpdating = False
         
        dataRange.Columns.Hidden = False
        dataRange.Rows.Hidden = False
         
        If UCase(Target.Value) <> "ALL" Then
        
            For Each col In dataRange.Columns
            
                col.Hidden = Application.CountIf(col, Target.Value) = 0
            Next
            
            For Each row In dataRange.Rows
            
                row.Hidden = Application.CountIf(row, Target.Value) = 0
            Next
        End If
         
        Application.ScreenUpdating = True
    End If
End Sub

[iamgujju]

Thank you so much.