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?”)