Run-time Error "13" when I delete contents of a cell

[Naomi_]

Hello All,

I'm brand new here and pretty new to Macros in general and I'm coming up on this error and cannot figure out what I'm doing wrong.
What I have set up is below, it all works just fine BUT, if I click delete on my keyboard to clear the contents of the reference cell, I get this error message.

The cell that this is looking at has a drop down menu of items to select. I have this exact coding in another sheet in my workbook and I am not getting this error message there.

Here is the code I'm using

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Activate
If Not Application.Intersect(Range("G17"), Range(Target.Address)) Is Nothing Then
Select Case Target.Value
'Salary Section - Show and Hide Code
Case Is = "Yes": Rows("42:204").EntireRow.Hidden = False
Rows("205:365").EntireRow.Hidden = True
Case Is = "No": Rows("42:204").EntireRow.Hidden = True
Rows("205:365").EntireRow.Hidden = True
Case Is = "": Rows("42:204").EntireRow.Hidden = False
Rows("205:365").EntireRow.Hidden = True
Case Is = " ": Rows("42:204").EntireRow.Hidden = False
Rows("205:365").EntireRow.Hidden = True

End Select
End If
End Sub

Any ideas??

Thanks!

 

[Fluff]

Hi & welcome to MrExcel.
Are you deleting the value in more than one cell?
Also is that cell merged?

 

[Naomi_]

Hi & welcome to MrExcel.
Are you deleting the value in more than one cell?
Also is that cell merged?

Thank you!

It is a merged cell yes and I'm only deleting it from the one location. I had tried adding Range ("G17:G19") instead and was still getting the same issue.

 

[Fluff]

Ok the merged cell is the problem & is yet another reason why you should never use merged cells, they are pure evil & will only cause you heartache & grief.

Are you willing to unmerge the cell?

Having had another look at the code, it appears that if No is selected rows 42:365 should be hidden & if anything else is selected then only rows 205:365 should be hidden.
Is that correct

 

[Naomi_]

I could unmerge the cell if absolutely necessary but would really prefer not to as it would require a lot of work to change my document, good to know they are evil, I'll avoid them in the future.

That is what I am trying to do yes, if it's no, then hide all the rows, if it's yes or anything else, hide only rows 205:365.

 

[Fluff]

Ok, how about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Not Intersect(Target, Range("G17")) Is Nothing Then
      Rows("42:204").Hidden = False
      If Range("G17").Value = "no" Then Rows("42:204").Hidden = True
   End If
End Sub

 

[Naomi_]

T

Ok, how about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Not Intersect(Target, Range("G17")) Is Nothing Then
      Rows("42:204").Hidden = False
      If Range("G17").Value = "no" Then Rows("42:204").Hidden = True
   End If
End Sub

Thank you!

That works brilliantly! So much cleaner too!

 

[Fluff]

You're welcome & thanks for the feedback.