Delete a row after user selects it. 1

[gjsala]

I have one object in cells A1 through A20 established from my code. The problem I'm having is I would like the user to select the object to be deleted by clicking on the cell, for example cell A13, and then the code would delete that row. Here's the code I'm having the problem with.

Thanks in advance.

Dim repl As Long
Dim d As Range, rngTotal As Range, myrange As Range, z As Integer
Dim message As String, title As String, Default As String
Dim numRows As Integer
Const strFind As String = "Samp-Mark"
Application.ScreenUpdating = False
Worksheets("Sheet1&quot.Activate
Application.ScreenUpdating = True
repl = MsgBox("Do you have any objects to delete?", vbYesNo)
If repl = vbYes Then
message = "Please select the object to delete." ' Set prompt.
title = "Object Delete" ' Set title.
Default = "User input here" ' Set default.
On Error Resume Next
Set myrange = Application.InputBox(message, title, Default, , , , , 8)
On Error GoTo 0
If myrange Is Nothing Then
MsgBox "Select a object!"
Exit Sub
End If
With Worksheets("Sheet1&quot.Range("A1:T1200&quot
Set d = .Find(What:=strFind, after:=Range(myrange.Address), _
LookIn:=xlValues, SearchOrder:=xlByColumns) ', LookAt:=xlWhole
If Not d Is Nothing Then
'found it
Rows(d).Delete
End If

 

[danomaniac]

gjsala,

You needed an End If and an End With statement at the end of your code...

try this:

Dim repl As Long
Stop
Dim d As Range, rngTotal As Range, myrange As Range, z As Integer
Dim message As String, title As String, Default As String
Dim numRows As Integer
Const strFind As String = "Samp-Mark"
Application.ScreenUpdating = False
Worksheets("Sheet1&quot.Activate
Application.ScreenUpdating = True
repl = MsgBox("Do you have any objects to delete?", vbYesNo)
If repl = vbYes Then
message = "Please select the object to delete." ' Set prompt.
title = "Object Delete" ' Set title.
Default = "User input here" ' Set default.
On Error Resume Next
Set myrange = Application.InputBox(message, title, Default, , , , , 8)
On Error GoTo 0
If myrange Is Nothing Then
MsgBox "Select a object!"
Exit Sub
End If
With Worksheets("Sheet1&quot.Range("A1:T1200&quot
Set d = .Find(What:=strFind, after:=Range(myrange.Address), _
LookIn:=xlValues, SearchOrder:=xlByColumns) ', LookAt:=xlWhole
If Not d Is Nothing Then
'found it
Rows(d).Delete
End If
End With
End If



Dan

"It's more like it is now, than it ever has been."

 

[danomaniac]

Oops... forgot something.

Take out the 'Stop' command, and change the following line:

Set d = .Find(What:=strFind, after:=Range(myrange.Address), _
LookIn:=xlValues, SearchOrder:=xlByColumns) ', LookAt:=xlWhole


to:

Set d = myrange

"It's more like it is now, than it ever has been."

 

[gjsala]

Dan,
The code works good until it reaches "Rows(d).delete" then an error says, "Type Mismatch" What can I do next?

Thanks.

 

[RobBroekhuis]

I think you want

d.entirerow.delete

(assuming you want to delete the row where the item was found).


Rob

 

[gjsala]

Thanks for all of your help.

Rob, your solution worked!

Thanks.