Logo 
Search:

MS Office Answers

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds
  Question Asked By: Vernon Day   on Aug 27 In MS Office Category.

  
Question Answered By: Sam Evans   on Aug 27

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

Share: 

 
 
Didn't find what you were looking for? Find more on Need to delete a row in excel based on user input Or get search suggestion and latest updates.


Tagged: