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 the empty 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"
.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"
.Range("A1:T1200"
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
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"
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"
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