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
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
8,433
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
 

SeanOZ

New Member
Joined
Oct 31, 2019
Messages
13
Hi,

Thank you so much for your quick response and help.

Kind regards,

Seán

?
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
41,710
Office Version
365
Platform
Windows
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
 

SeanOZ

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

Thanks again for your help.

This is awesome.

Kind regards,

Sean
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
41,710
Office Version
365
Platform
Windows
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
 

Forum statistics

Threads
1,077,784
Messages
5,336,329
Members
399,076
Latest member
vullistax

Some videos you may like

This Week's Hot Topics

Top