PDA

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



iamgujju
06-26-2019, 11:29 AM
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.24500




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
06-26-2019, 11:46 AM
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
06-26-2019, 11:54 AM
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
06-26-2019, 12:08 PM
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
06-26-2019, 12:21 PM
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
06-26-2019, 12:34 PM
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
06-26-2019, 12:41 PM
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
06-26-2019, 01:13 PM
[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.: pray2:

Bob Phillips
06-26-2019, 02:58 PM
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
06-27-2019, 02:45 AM
Thank you so much.