"ClearContents" command halts code

[Dave O]

I have a routine that highlights a range and removes any entries in
that range. For some reason the ClearContents command halts processing
AFTER it has performed the removal of entries. If I comment that line
out the routine runs as expected.

The code is
Sub PoP5()
If Sheets("Intro").Range("h16").Value = False Then
Sheets("Labor").Select
Worksheets("Labor").Unprotect Password:="xxx1"
Range("ba4:ba53").ClearContents
Columns("AU:BB").Select
Selection.EntireColumn.Hidden = True
{snip}

With the commented out, the columns hide and processing continues;
with the ClearContents active the entries are removed but processing
stops. I tried breaking that line into two, comme ca:
Range("ba4:ba53").Select
Selection.ClearContents
....to no avail, the same thing happens.

Anyone have thoughts for the cause, or a workaround?

Thanks

 

[Gord Dibben]

This works fine for me if "Intro" sheet H16 = False

Clears the contents then hides the columns and pops up the message box with
"done"

You're saying that the columns do not get hidden on "Labor" sheet?

Sub PoP5()
If Sheets("Intro").Range("h16").Value = False Then
Sheets("Labor").Select
Worksheets("Labor").Unprotect Password:="xxx1"
Range("ba4:ba53").ClearContents
Columns("AU:BB").Select
Selection.EntireColumn.Hidden = True
End If
MsgBox "done"
End Sub

Note: you don't need the select to hide the columns.

Columns("AU:BB").EntireColumn.Hidden = True


Gord Dibben MS Excel MVP

 

[Dave Peterson]

Do you have any event macros that could be messing up?

Sub PoP5()
If Sheets("Intro").Range("h16").Value = False Then
with workSheets("Labor")
.Unprotect Password:="xxx1"
application.enableevents = false
.Range("ba4:ba53").ClearContents
application.enableevents = true
.Columns("AU:BB").EntireColumn.Hidden = True
end with
end if

I also deleted the .select's.

 

[Dave O]

Thanks, Gord and Dave. It was, in fact, an event macro: I'd altered it
to solve one problem, and wound up creating another.