Create a basic macro button for removing specific rows or columns

SeanOZ

New Member
Joined
Oct 31, 2019
Messages
13
Hi all,

Apologies for the simple request as a beginner to the forum, but I would like to have a button on a spreadsheet that I can delete either a specific column or row.

I have code to remove a column as follows:
Sub DeleteColumns()
Dim varUserInput As Variant
varUserInput = InputBox("Enter Column Letter where you want to delete a column:", _
"What Column?")
If varUserInput = "" Then Exit Sub
Columns(varUserInput).Delete
Exit Sub
M:
MsgBox "You did not enter a letter character into the InputBox" & vbNewLine & "Try again"
End Sub

Basically I would like a button to delete a row or alternatively, a button to delete either a row or column.

I appreciate any feedback for the same.

Kind regards,

Sean
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Try:
Code:
Sub DelRowCol()
    Application.ScreenUpdating = False
    Dim response As String
    response = InputBox("Enter the column letter of the column ot be deleted or" & Chr(10) & "the row number of the row to be deleted.")
    If response = "" Then
        MsgBox "You did not enter a letter character or a number into the InputBox." & vbNewLine & "Try again."
        Exit Sub
    End If
    If IsNumeric(response) Then
        Rows(response).EntireRow.Delete
    Else
        Columns(response).EntireColumn.Delete
    End If
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
In case it is of use to you, adding the blue code below would also allow you to remove multiple rows or multiple columns at once by entering values like 6:10 or D:J into the input box.
I have also added the red code to stop the code erroring if the user happens to enter something that is not able to be interpreted as rows or columns (eg K2)
Rich (BB code):
Sub DelRowCol()
    Application.ScreenUpdating = False
    Dim response As String
    response = InputBox("Enter the column letter of the column ot be deleted or" & Chr(10) & "the row number of the row to be deleted.")
    If response = "" Then
        MsgBox "You did not enter a row or column reference into the InputBox." & vbNewLine & "Try again."
        Exit Sub
    End If
    On Error Resume Next
    If IsNumeric(Left(response, 1)) Then
        Rows(response).EntireRow.Delete
    Else
        Columns(response).EntireColumn.Delete
    End If
    On Error GoTo 0
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
Hi Peter,

Thanks again for your help.

This is awesome.

Kind regards,

Sean
You're welcome. Thanks for the follow-up. :)

BTW, there is some redundant code still in there if you want to tidy it up.
EntireRow is not required
Code:
Rows(response)<del>.EntireRow</del>.Delete
Similar for EntireColumn
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,140
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top