Automatically Hiding Rows when certain cell value has been selecte

  • Thread starter Thread starter Jason
  • Start date Start date
J

Jason

I am trying to hide rows 13-16 when a certain value has been selected from a
named range that contatins a drop down list.

Any help will be greatly appreciated.

Regards
 
Select the sheet tab which you want to work with. Right click the sheet tab
and click on 'View Code'. This will launch VBE. Paste the below code to the
right blank portion. Get back to to workbook and try out.

-Change the range and the text string to suit your requirement

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("A13:A16")) Is Nothing Then
If Target.Count = 1 And Target.Text = "3" Then Rows(Target.Row).Hidden = True
End If
Application.EnableEvents = True
End Sub

If this post helps click Yes
 
Hi Jacob,

Thanks for coming back to me so quickly. However, this did not work.

The drop down list is in a range of cells named "LocationInput" (E4:J4), and
when the value of 11599 (DCM) is selected (this is in the drop down list). I
would like rows 13,14,15 & 16 to be hidden.

I have also named the 4 rows that are to be hidden as "RowsToHide".

There are only 3 values in the drop down list, so when one of the other
values is selcted, then the 4 rows should unhide.
 
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "Locationinput"
Dim cell As Range
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
Application.EnableEvents = False
For Each cell In Target
With Target
If .Value = "11599 (DCM)" Then
Range("Rowstohide").EntireRow.Hidden = True
Else
Range("Rowstohide").EntireRow.Hidden = False
End If
End With
Next cell
End If
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP
 
Back
Top