Option Explicit
Sub testme2()

Dim myRng As Range
Dim myListObject As ListObject
Dim RowToDelete As Range

Set myRng = Nothing
On Error Resume Next
Set myRng = Application.InputBox _
(Prompt:="Select a single cell in " _
& "the row of the list to delete", _
Type:=8).Cells(1) 'single row only!
On Error GoTo 0

If myRng Is Nothing Then
Exit Sub 'user hit cancel
Else
Set myListObject = Nothing
On Error Resume Next
Set myListObject = myRng.ListObject
On Error GoTo 0

If myListObject Is Nothing Then
'do nothing--not in a list
msgbox "not in a list!"
Else
Set RowToDelete = Nothing
On Error Resume Next
Set RowToDelete = Intersect(myListObject.Range, myRng.EntireRow)
On Error GoTo 0
If RowToDelete Is Nothing Then
'do nothing
Else
RowToDelete.Delete shift:=xlShiftUp
End If
End If
End If

End Sub


Beeo wrote:

Thanks - It's working

To make it a little more interesting would it be hard to only delete
the data within the list (excel 2003) and not the whole row. The same
way as if the user click on a row within the list - and then "delete
row" from the drop down in the floating list menu.

Please give a hint and I'll try if I can solve it myself - thanks

Beeo

On 1 Mar, 14:43, Dave Peterson wrote:
Ask for them to point and click on a range (using type:=8)

Dim myRng as range
set myrng = nothing
on error resume next
set myrng = application.inputbox(Prompt:="Select a range of rows to delete", _
type:=8).areas(1) 'single area only!
on error goto 0

if myrng is nothing then
exit sub 'user hit cancel
else
myrng.entirerow.delete
end if

If you only wanted to allow them to delete a single row at a time:

set myrng = application.inputbox(Prompt:="Select a range of rows to delete", _
type:=8).cells(1)

Beeo wrote:

Hello

I have a data-list (excel 2003) with records and the following code

RowNo = Application.InputBox(Prompt:="Click on the row you wish to
delete", Title:="Delete row", Type:=1 + 2)
Selection.ListObject.ListRows(RowNo).Delete

But it's not working. Help appreciated (I'm new to this so please be
nice :)

Tia / Beeo

--

Dave Peterson

--

Dave Peterson