Help with an error (1004)

[Tobbes]

I cant seem to work out why this errors:

Sub ReturnToStart()
ret = MsgBox("Are you sure you want to reset all the completions and start a new HSC servicing?", vbYesNo + vbCritical, "Confirm Reset")
If ret = vbYes Then
    For a = 1 To 5
    For c = 1 To 65536
        If Sheets("HSC 3 Day " & a).Cells(c, 6).Value = "P" Then
            Sheets("HSC 3 Day " & a).Cells(c, 6).Value = ""
        Else
        End If
    Next
    Next
    Sheets("CFUs").Range("B4", Range("B4").End(xlDown).Offset(0, 3)).ClearContents
    Call Completer
Else
End If
End Sub

It errors on this code "Sheets("CFUs").Range("B4", Range("B4").End(xlDown).Offset(0, 3)).ClearContents"
However if I use Range("B4:E46"), instead of the dyanmic down-and-across, it works....

 

[GlennUK]

Sheets("CFUs").Range("B4", Sheets("CFUs").Range("B4").End(xlDown).Offset(0, 3)).ClearContents

 

[Weaver]

I think glenn has answered your question but you might like to consider an alternative method for another part of your code

I think

    For c = 1 To 65536
        If Sheets("HSC 3 Day " & a).Cells(c, 6).Value = "P" Then
            Sheets("HSC 3 Day " & a).Cells(c, 6).Value = ""
        Else
        End If
    Next

can be replaced with:

    Sheets("HSC 3 Day " & a).Columns(6).Replace what:="P", replacement:="", lookat:=xlWhole

 

[Tobbes]

Thank you both. MrExcel continues to teach me something new everyday.
You guys are the difference between Intelligence and genius.