Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Need to delete a row in excel based on user input

  Asked By: Vernon    Date: Aug 27    Category: MS Office    Views: 2300
  

My boss wants me to become a VB guru. He won't send me to a class
but, I can buy a couple of books. I have ordered a couple of books
but, he wants results now. HELP!

I have a budget worksheet that needs some macros. I want to add a
button that will ask the user to enter the row or rows to be deleted
into a text box, the user then clicks OK on the user form and the row
or rows are deleted.

Also, I am supposed to add a button that will allow for the pasting of
data in from other work sheets.

I'm a really new user and my boss wants this asap. The delete is the
highest priority. Your help will be greatly appreciated!!

Share: 

 

1 Answer Found

 
Answer #1    Answered By: Sam Evans     Answered 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

 
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: