Insert / Name / Define

[Katherine R]

I have a macro that should delete the contents of a named range, but it's not
deleting cell H17. In the Refers to box the range is defined as
='Sheet1'$H$10:$H$17. H10 through H16 deletes, but I have to manually delete
H17. What goes here?

Thanks.

 

[Niek Otten]

Your code?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I have a macro that should delete the contents of a named range, but it's not
| deleting cell H17. In the Refers to box the range is defined as
| ='Sheet1'$H$10:$H$17. H10 through H16 deletes, but I have to manually delete
| H17. What goes here?
|
| Thanks.

 

[Harlan Grove]

I have a macro that should delete the contents of a named range, but it's not
deleting cell H17. In the Refers to box the range is defined as
='Sheet1'$H$10:$H$17. H10 through H16 deletes, but I have to manually delete
H17. What goes here?

How is it really defined? The expression above is a syntax error since
there's no ! between the worksheet name and the range address. You may
believe it's defined to span H10:H17, but double check.

Also, are you using .Clear or .ClearContents method calls? What's the actual
macro statement you're using that doesn't work?

 

[Katherine R]

Sorry for the typo... ='Sheet 1'!$H$10:$H$17

It's doing as it should for all but cell H17

 

[Harlan Grove]

Sorry for the typo... ='Sheet 1'!$H$10:$H$17

It's doing as it should for all but cell H17

....

OK, show the macro statement that isn't working.

 

[Katherine R]

Sub Clear_Click()
If ActiveSheet.ProtectContents Then
ActiveSheet.Unprotect
Range("Timedata").ClearContents
Range("Timedata2").ClearContents
Range("Afterhours").ClearContents
Range("TimeComments").ClearContents
Range("HolidayTaken").ClearContents



ActiveSheet.Protect _
DrawingObjects:=True, _
Contents:=True
Else
Range("Timedata").ClearContents
Range("Timedata2").ClearContents
Range("Afterhours").ClearContents
Range("TimeComments").ClearContents
Range("HolidayTaken").ClearContents
End If
End Sub