Delete a row after user selects it.

[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 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&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

 

[RobBroekhuis]

I'm not entirely sure what you're trying to do, but it seems to me you could make this work with a RefEdit control on a simple Userform. If that sounds like gibberish, check back and we'll explain.


Rob

 

[gjsala]

Rob,
I'm not sure what you mean. Maybe I can explain this a little different. First, the code will prompt the user to select a cell to be deleted. Second, the user will select a cell in column A. Third, from the message box, the cell location will be stored as a variable. Fourth, after that the code will delete the whole row from the cell that was selected. If this still doesn't make since, please let me know.

Thanks.

 

[RobBroekhuis]

Yes, that's how I interpreted your question. And it's best handled with a simple userform. Are you familiar with using those? They allow you to use a RefEdit control, which lets your user enter a cell address (or range) in a way that's consistent with Excel's user interface.


Rob

 

[gjsala]

No I'm not familiar with userform, could you give me an example?

Thanks.

 

[RobBroekhuis]

Userforms are an integral component of VBA. An explanation of them would take too long here, so I recommend you find a tutorial (you can probably find one on the web, or else pick up a VBA book). In the meantime, you can try to play around: in the VB editor, right-click on the name of your project in the project explorer (left on your screen), and choose insert - userform. That will show a blank userform window, as well as the controls toolbox. You can then drag and drop controls onto your userform, and edit them. The one I was referring to, RefEdit, is the last, non-descript, icon in the toolbox. Once you've put together the framework, see how it works by executing the statement (from a sub, or from the immediate window) userform1.show
Good luck!


Rob