Create a basic macro button for removing specific rows or columns

[SeanOZ]

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

 

[mumps]

Try:

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

 

[SeanOZ]

Hi,

Thank you so much for your quick response and help.

Kind regards,

Seán

?

 

[Peter_SSs]

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)

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

 

[SeanOZ]

Hi Peter,

Thanks again for your help.

This is awesome.

Kind regards,

Sean

 

[Peter_SSs]

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

Rows(response)<del>.EntireRow</del>.Delete

Similar for EntireColumn

 

[SeanOZ]

Hi,

Thanks again for your all your help.

Kind regards,

Sean