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. |
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. |
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. |
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. |
All times are GMT +1. The time now is 04:03 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com