Macro to hide rows when data validation list changes

[aklewe]

Good morning! I am so glad to have found this site, its so helpful.


I wish to have a macro that runs when the selected value of a data validation list changes in cell B4. It doesnt matter what the value is, just that it changed.


The purpose of the macro will be to hide rows where the value in C8:C120 equals zero; this value will change based on the selection from the list. I believe I got the hiding rows part down, thanks to an older post found here:


Range("C8:C120").Select

For Each cell In Selection

If cell = 0 Then

'Range(cell.Address).EntireRow.Hidden = True

End If

Next


But could someone help me with adding a line or two to make the above occur when the value in B4 changes? Thanks much!!


aklewe

*happy excel geek*

 

[Pablo]

Hi,

Here is the code. Just make sure you save it in the actual sheet where you have the data, NOT as a module.


Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$C$4" Then

Application.ScreenUpdating = False

Range("C8:C120").Select

For Each cell In Selection

If cell = 0 Then

Range(cell.Address).EntireRow.Hidden = True

End If

Next

End If

End Sub

 

[aklewe]

Hi Pablo,


Thanks for the help. However, with the macro in place, I cannot change the selection in the drop down list as the macro runs anytime I click in that cell.


Anyone have suggestions?


aklewe

*happy excel geek*

 

[Pablo]

Hi Aklewe,

It works fine for me. The macro should run only when you change the cell, not when you select it. You said any change, but if you need a validation, set it up 1st, to do that:

- Create a list anywhere in the sheet

- It's better to name that list

- Click Data on the Ribbon, then Data Validation

- Under Allow: select list

- Under Source: type the name you gave to your list or input the range. Hit OK.


Now the macro always does the same thing regardless of your selection, hide rows when cells are zero in the range C8 to C120.


I hope this helps.

Pablo