How to Clear Cell Contains using VB on Excel

TGoldenEye

Though it might be tempting to use cialis cialis uk or some similar medication to enhance sex play, the risks far outweigh the benefits. The branded viagra sample pills has lots of ads and appointed medical representatives. But the amount of generico cialis on line icks.org pain suffered during and after the treatment. This solution is Generic Protonix and its effects need to be experienced through its implementation and routine consumption we can easily put control over our unregulated heart beats and can bring our blood pressure level back to the ideal level in order to prevent the developments of cardiac dysfunctions. cialis ordering  

How to Clear Cell Contains on MS Excel

In this How To, I will show how to clear items on Excel cells with VB code. I have created a sample sheet of calculating age from the Date of Birth shown below. You will need to put the VB code under a command button. After clicking the button, it will clear the specific cells as specified on the VB code. This How To will show the step by step below.

 Step 1. Add a Button

  • Under the Developer Menu, click Insert icon
  • Select a Button icon from the Form Controls section
  • Drag and place the button on Excel sheet.

  • the Assign Macro window will open
  • You can change the button name and Macro name

  • Click on New button
  • A Microsoft Visual Basic for Applications will open as shown below. The VB code will be saved under the Module that can be used on any sheet under this workbook

Step 2. Enter VB code

  • Using a ClearContents Command to clear the value in the specified cells
  • Clear one cell: use full code as Worksheets(“Sheet1”).Range(“C3”).ClearContents inside the Button1_Click() procedure shown below. After click a button, it will clear or delete the value on cell C3 (the Date of Birth for Nancy Thao) on Sheet1

  • Clear range of cells: as Worksheets(“Sheet1”).Range(“A3:C300”).ClearContents. It will clear or delete the value from cell A3 to C300 on Sheet1.

Full Code under new Clear All button:

Sub Clear_All()

    Worksheets("Sheet1").Range("A3:C3100").ClearContents

End Sub

NOTE:
After you click the button to clear contains, you cannot undo the process or the undo command is no longer working to bring the information back.

            

Step 3. Edit VB code

You can edit the VB code under the button by using Right-Hand click on the button you want to edit the code. For example: I want to edit the VB code under the Clear All button

  • Right-Hand click on Clear All button
  • Select Assign Macro…

  • The Assign Macro window will open
  • Select Macro name on Clear_All
  • Click Edit button

The Microsoft Visual Basic for Applications will open and set focus on the procedure of the Clear_All Macro name that you want to edit the VB.

Sub Clear_All()
Worksheets("Sheet1").Range("A3:C3100").ClearContents
End Sub

 

Confirm before Clear

As I mentioned earlier that you cannot undo the process or cannot retrieve the deleted information after you click Clear button, so the best practice is to ask for confirmation before clearing the contains.  See example below:

Explanation:

Per VB code above, you will get a message pop-up window after you click Clear All button. It asks you “Are you sure you want to clear all customer information?” with the response buttons (Yes or No).

Yes: Confirm to clear the contains

No: Cancel the process or confirm not to clear contains

Example of Clear One Cell and Range of Cells

We can use comma (,) between the cells, group of cells, or range of cells inside the Range Function on the line: Worksheets(“Sheet1”).Range(“B3, C3:C3100”).ClearContents

Full Code:
Sub Clear_All()
Dim Response As Integer

Response = MsgBox("Are you sure you want to clear all customer information?", vbYesNo, "Clear All?")
If Response = vbYes Then
    Worksheets("Sheet1").Range("B3, C3:C3100").ClearContents
Else
    'do nothing
End If
End Sub


Example of Using ClearContents command more than one line

It will be too long in one line of codes if we use many commas (,,,,,) between cells we want to clear. To be look nice and professional, we can use more than one line of ClearContents command as shown below.

Full Code:

Sub Clear_All()
Dim Response As Integer

Response = MsgBox("Are you sure you want to clear all customer information?", vbYesNo, "Clear All?")
If Response = vbYes Then
   Worksheets("Sheet1").Range("A3:C3").ClearContents
   Worksheets("Sheet1").Range("A4:C300").ClearContents
  Worksheets("Sheet1").Range("H1, H3:H15, K1: K50").ClearContents
Else
     'do nothing
End If
End Sub

Example of Using ClearContents in Columns

It will clear all contains or values in the specified columns in the code. The columns index 1 = Column A. You can use the column letter in the code as .Colunn(“D”) = Column D.

Clear Columns:

Sub Clear_All()
Dim Response As Integer

Response = MsgBox("Are you sure you want to clear all customer information?", vbYesNo, "Clear All?")

If Response = vbYes Then
    With Sheets("Sheet1")
       .Columns(1).ClearContents  'Column A
       .Columns(4).ClearContents   'Column C
       .Columns("D").ClearContents  'Column D
    End With
Else
'do nothing
End If
End Sub

Extra Credit on this How To:

Set Focus on the Response Button

In the earlier example, a prompt of the response button is set to Yes button when the message window pops up using VB code: vbYesNo

Response = MsgBox(“Are you sure you want to clear all customer information?”, vbYesNo, “Clear All?”)

In the earlier example, a prompt of the response button is set to Yes button when the message window pops up. If the users accidentally press Enter on keyboard after the message pops up, it will clear/delete information right away. The information cannot be retrieved back by the Undo command as mentioned earlier. We can set the focus of the response to No button by adding a vbDefaultButton2 to vbYesNo as shown in the line below.

Response = MsgBox(“Are you sure you want to clear all customer information?”, vbYesNo + vbDefaultButton2, “Clear All?”)

 

Related posts