The following code will display a special inputbox that will let your users
select cells in the rows to be deleted, by using a mouse or typing cell
addresses. Copy & paste the code below into a VBA module in your workbook. Add
the button to your worksheet and set its Click event to call the DeleteRows
subroutine.
Public CurrSht As Worksheet
Public Sub DeleteRows()
Dim UserSel As String
'Display special form for user to select cells in the row(s) to delete.
UserSel$ = GetUserRange
'If user didn't select a cell, stop execution.
If Len(UserSel$) = 0 Then
Exit Sub
End If
'Otherwise, delete the row(s) containing the selected cell(s).
CurrSht.Activate
CurrSht.Range(UserSel$).EntireRow.Delete
End Sub
Public Function GetUserRange() As String
'Adapted from code found on j-walk.com
Dim UserRange As Range, Prompt As String, Title As String
Prompt = "Select a cell in the row(s) to be deleted."
Title = "Select cells"
'Display the input Box
On Error Resume Next
Set UserRange = Application.InputBox( _
Prompt:=Prompt, _
Title:=Title, _
Default:=ActiveCell.Address, _
Type:=8) 'Type 8 is Range selection
'Was the Input Box canceled?
If UserRange Is Nothing Then
GetUserRange = vbNullString
Exit Function
End If
'Set global variable CurrSht to the worksheet containing UserRange
Set CurrSht = UserRange.Parent
'Return the address of the range selected
GetUserRange = UserRange.Address
End Function