Clear contents on specified ranges

[Piotr (Peter)]

Hi,

I have the below code:

Private Sub CommandButton90_Click()
Dim Answer As String
Dim MyNote As String


MyNote = "Are you sure? This action will clear ALL entered data in this
excel file"


Answer = MsgBox(MyNote, vbQuestion + vbYesNo, "All codes will be deleted")

If Answer = vbNo Then
Exit Sub
Else


Sheets("Sheet1").Range("D39" & "D1119" & "D2124" & "D2635" &
"D3739" & "H39" & "I3:I14" & "I16:I26" & "I28:I37" & "I39:I44" & "N3:N12" &
"N14:N31" & "N33:N44").ClearContents


End If

End Sub

Whenver I run this macro it stops where the line with ClearContents is
showin Run time error 1004. I would like ot clear specified ranges as well as
a cell h39. I am just not sure what is wrong. I have tried without quotes and
& sign (I have used comma instead) but no avail. I'm new to VBA so any help
appreciated as I could not find solution neither here nor on google.

 

[Jacob Skaria]

Try

Dim MyNote As String

MyNote = "Are you sure? This action will clear ALL entered data in this
excel file"
If MsgBox(MyNote, vbQuestion + vbYesNo, "Codes will be deleted") <> vbYes Then
Exit Sub
Else
Sheets("Sheet1").Range("D39,D1119,D2124,D2635,D3739,H39," & _
"I3:I14,I16:I26,I28:I37,I39:I44,N3:N12,N14:N31,N33:N44").ClearContents
End If

 

[Piotr (Peter)]

Thanks Jacob for your prompt response. It worked like a charm.
I was just wondering why do I have to use seuch structure "......., H39," & _
"......."? Is it because of that single cell H39 or could I do without &
_ ??

Thanks.

 

[Mike Fogleman]

The &_ is used to continue the code on a new line so Excel will read the 2
lines as one. Jacob wrote it that way so if you copied the code from the
newsreader and pasted to your code module, the code would work as written.
Otherwise, the newsreader would have truncated the long list of ranges onto
a second line anyway, and a copy\paste of that would not work in VB module.

Mike IF