Marco not working after inserting new rows

[1q2w3]

Hi all,
Please can anyone help with this?
I've used the following but if I insert a new row before the selected rows (i.e. before row 16), the code then doesn't work anymore. How can I make sure the code is locked to the cell I selected?

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Activate
If Not Application.Intersect(Range("F14"), Range(Target.Address)) Is Nothing Then
    Select Case Target.Value
    Case Is = "A":
                Rows("16:32").EntireRow.Hidden = True 
    Case Is = "B":
                Rows("16:19").EntireRow.Hidden = False
                Rows("20:32").EntireRow.Hidden = True
    Case Is = "C":
                Rows("16:19").EntireRow.Hidden = True
                Rows("20:31").EntireRow.Hidden = False
                Rows("32").EntireRow.Hidden = True
    Case Is = "D":
                Rows("16:31").EntireRow.Hidden = True
                Rows("32").EntireRow.Hidden = False
    End Select
End If

 

[DanteAmor]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Activate
If Not Application.Intersect(Target, Range("F14")) Is Nothing Then

if target.count > 1 then exit sub

Select Case Target.Value
Case Is = "A":
Rows("16:32").EntireRow.Hidden = True
Case Is = "B":
Rows("16:19").EntireRow.Hidden = False
Rows("20:32").EntireRow.Hidden = True
Case Is = "C":
Rows("16:19").EntireRow.Hidden = True
Rows("20:31").EntireRow.Hidden = False
Rows("32").EntireRow.Hidden = True
Case Is = "D":
Rows("16:31").EntireRow.Hidden = True
Rows("32").EntireRow.Hidden = False
End Select
End If

 

[1q2w3]

This doesn't seem to work, when I add a new row - 'F14' didn't response to hiding the selected rows.
Any other ideas?

 

[Peter_SSs]

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block at the bottom of this post has more details. I have added the tags for you this time.

Try this (post back if you need help with any of it)

1. Give cell F14 a name. I used "DVcell" since I guessed you might have those letters as Data Validation choices in that cell.
   
   
2. Give, say, F16:F32 a name. This doesn't have to be column F but any range that includes all those rows. I used the name "myRows".
   
   
3. Now try this worksheet change code.

The code should continue to work no matter what rows are added/deleted provided the ""DVcell" and "myRows" named ranges exist (and "myRows" has at least 17 rows).

Note that I have added an extra section that unhides all the rows if some other value appears in the "DVcell" or if that cell is cleared.
If you don't want that section then just remove the two relevant lines

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Application.Intersect(Target, Range("DVcell")) Is Nothing Then
    With Range("myRows").EntireRow
      Select Case Range("DVcell").Value
        Case Is = "A"
          .Rows("1:17").Hidden = True
        Case Is = "B"
          .Rows("1:4").Hidden = False
          .Rows("5:17").Hidden = True
        Case Is = "C"
          .Rows("1:4").Hidden = True
          .Rows("5:16").Hidden = False
          .Rows(17).Hidden = True
        Case Is = "D"
          .Rows("1:16").Hidden = True
          .Rows(17).Hidden = False
        Case Else
          .Hidden = False
      End Select
    End With
  End If
End Sub