Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Excel 2003 macro - clear cells

Posted on 2011-10-17
15
Medium Priority
?
217 Views
Last Modified: 2012-05-12
I have used this to clear cells from a cmomand button in Excel. It is not working on a newly createed worksheet.What are the other options to clear cell contents

Sub Clear()
'
If MsgBox("Have You Sent Form? Are You sure you want to clear form?", vbYesNo + vbQuestion, "Confirm") = vbYes Then
 
                [C1] = ""
                [C2] = ""
                [C3] = ""
   
End If
'
End Sub
0
Comment
Question by:Pdeters
  • 3
  • 3
  • 3
  • +4
15 Comments
 
LVL 33

Expert Comment

by:jppinto
ID: 36980503
Cells(1,3).Value=""
Cells(2,3).Value=""
Cells(3,3).Value=""
0
 

Author Comment

by:Pdeters
ID: 36980528
doesn't work
0
 
LVL 33

Expert Comment

by:jppinto
ID: 36980568
Do you get any error? Where?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 27

Expert Comment

by:Glenn Ray
ID: 36980574
Here's another possible solution
Sub Clear()
If MsgBox("Have You Sent Form? Are You sure you want to clear form?", vbYesNo + vbQuestion, "Confirm") = vbYes Then
    Range("C1").Value = ""
    Range("C2").Value = ""
    Range("C3").Value = ""
.
.
.
End If
End Sub

Open in new window

0
 
LVL 19

Expert Comment

by:regmigrant
ID: 36980577
When you say it doesn't work on a 'newly created worksheet' what do you mean? How did you move it to the new sheet and where did you save the module ?
0
 
LVL 19

Expert Comment

by:regmigrant
ID: 36980591
sorry, submmitted before I finished - is it created within the worksheet or within the workbook section ?
0
 
LVL 33

Expert Comment

by:jppinto
ID: 36980619
response =MsgBox("Have You Sent Form? Are You sure you want to clear form?", vbYesNo + vbQuestion, "Confirm")
If response = vbYes then
   Cells(1,3).Value=""
   Cells(2,3).Value=""
   Cells(3,3).Value=""
End If
0
 
LVL 9

Expert Comment

by:experts1
ID: 36980655
Sub Clear()
'
If MsgBox("Have You Sent Form? Are You sure you want to clear form?", vbYesNo + vbQuestion, "Confirm") = vbYes Then
 
                Range("C1").ClearContents
                Range("C2").ClearContents
                Range("C3").ClearContents
   
End If
'
End Sub
0
 

Author Comment

by:Pdeters
ID: 36981679
Nothing happens when I run the macro. I put in the macro and i get the confimr yes or no. But then the cells do not clear
0
 
LVL 9

Expert Comment

by:experts1
ID: 36981904
Please submit the name of the specific worksheet you have open
and active while running the macro!


 
0
 

Author Comment

by:Pdeters
ID: 36982069
Sheet1
0
 
LVL 9

Expert Comment

by:chwong67
ID: 36983152
You need to assign macro to the cmomand  button.
Clear.xls
0
 
LVL 42

Expert Comment

by:dlmille
ID: 36984239
This assumes you want to run this on the Active Sheet.  Most if not all the solutions provided heretofore should work on the active sheet.  Ensure the sheet with the cells to be cleared is active (visible and up front) before running the macro.

If you want to clear the cells on a specific sheet, then specify that in front of the Range, like:

Sheets("thisSheet").Range(etc.....

or make the relevant sheet active, first, like:

Sheets("thisSheet").Activate... (however, I'm a fan of prefixing the range objects with the worksheet, like the below):

dim wks as worksheet

set wks = sheets("thisSheet")

wks.Range("whatever").clearcontents....

At any rate, you can clear with one command, not needing 3 - see below:
--------------------------------------------------------------------------------------
3 different method "one-liners", below:

Sub Clear()

  If MsgBox("Have You Sent Form? Are You sure you want to clear form?", vbYesNo + vbQuestion, "Confirm") = vbYes Then
      Range("C1:C3").ClearContents '<- probably the most efficient, or you can do the next command
      Range("C1:C3").Value = vbNullString 'or use the "" approach, below
      Range("C1:C3").Value = "" 'or
  End If
End Sub
0
 
LVL 9

Accepted Solution

by:
experts1 earned 2000 total points
ID: 36985310
This should work now!

If not, please check if the "Sheet1" is protected.

Sub Clear()
'
If MsgBox("Have You Sent Form? Are You sure you want to clear form?", vbYesNo + vbQuestion, "Confirm") = vbYes Then
 
                Sheets("Sheet1").Range("C1").ClearContents
                Sheets("Sheet1").Range("C2").ClearContents
                Sheets("Sheet1").Range("C3").ClearContents
End If
'
End Sub
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37140326
I don't really want to know...


OK - maybe I do... Was it a sheet protection issue?  Because there were LOTS of solutions posted earlier that should have sufficed

:)
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

579 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question