Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clear contents on specified ranges
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("D3:D9" & "D11:D19" & "D21:D24" & "D26:D35" & "D37:D39" & "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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clear contents on specified ranges
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("D3:D9,D11:D19,D21:D24,D26: D35,D37:D39,H39," & _ "I3:I14,I16:I26,I28:I37,I39:I44,N3:N12,N14:N31,N33 :N44").ClearContents End If -- Jacob "Piotr (Peter)" wrote: 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("D3:D9" & "D11:D19" & "D21:D24" & "D26:D35" & "D37:D39" & "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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clear contents on specified ranges
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. "Jacob Skaria" wrote: 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("D3:D9,D11:D19,D21:D24,D26: D35,D37:D39,H39," & _ "I3:I14,I16:I26,I28:I37,I39:I44,N3:N12,N14:N31,N33 :N44").ClearContents End If -- Jacob "Piotr (Peter)" wrote: 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("D3:D9" & "D11:D19" & "D21:D24" & "D26:D35" & "D37:D39" & "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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clear contents on specified ranges
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 "Piotr (Peter)" wrote in message ... 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. "Jacob Skaria" wrote: 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("D3:D9,D11:D19,D21:D24,D26: D35,D37:D39,H39," & _ "I3:I14,I16:I26,I28:I37,I39:I44,N3:N12,N14:N31,N33 :N44").ClearContents End If -- Jacob "Piotr (Peter)" wrote: 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("D3:D9" & "D11:D19" & "D21:D24" & "D26:D35" & "D37:D39" & "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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Clear Contents | Setting up and Configuration of Excel | |||
Clear Contents | Excel Discussion (Misc queries) | |||
Clear Contents of multiple continuous ranges | Excel Programming | |||
Macro to clear range contents when cell contents are changed by us | Excel Programming | |||
Clear contents | Excel Programming |