Button to delete a row

diana - Sep 17, 2009 at 06:39 AM
 David - May 9, 2013 at 08:04 AM

i'm trying to create a comand button on excel to delete a slected row, using vb,
i need to know the whole procedure and the code
appreciate ur help.

1 reply

Hi Diana,

I will try and help you, but I'm using a dutch version of excel and don't know the exact translations of the option.

Start by creating a button:
Select the third option from the left on the option bar.
Then select the forth option from the top (toolbars) and click on Visual Basic.
Now click on the 2nd and 3rd option from the right on the Visual Basic toolbar.
A new toolbar will be displayed.
On this toolbar select the command button option (6th from the left).
Draw your button anywhere you want.
On the same toolbar click on the 2nd option from the left.
Change the text displayed on your button by changing the text next to Caption.
The text next to (Name) will be used by VBA to refer to this button. The default for your first button is CommandButton1.
Take a look at the other option and then close the window.
Now click again on the 2nd and 3rd option from the right on the Visual Basic toolbar.
Now it's time to open VBA by pressing Alt+F11.
In VBA select the 4th option from the left (insert?) and click on Module.
Copy the following code in the module:

Sub DeleteRow()
End Sub

On the left side of VBA above the module are your sheets displayed.
Double click the sheet on which you created the button and enter the following code:

Private Sub commandbutton1_click()
End Sub

Now close VBA and you are done.
Select a cell, click the button and the entire row containing the selected cell will be deleted.

Please be sure to know that you can't use the blue arrows to undelete the row, once deleted it can only be retrieved by closing without saving and then reopening your file.

Don't hesitate to ask me if something is still unclear.

Best regards,
This is an awesome code. Thanks