Remove row in list based on user input

  • Thread starter Thread starter Beeo
  • Start date Start date
B

Beeo

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
 
see if following will do what you want?

Sub DeleteRow()
Dim Rowno As Variant
On Error Resume Next
Set Rowno = Application.InputBox(Prompt:="Click on the row you wish to
delete", Title:="Delete Row", Type:=8)
If VarType(Rowno.Value) = vbBoolean Then
If Rowno = False Then
Debug.Print "user cancelled"
Exit Sub
End If
End If

Range(Rowno.Address).EntireRow.Delete

End Sub
 
MsgBox Prompt:="Click on the row you wish to delete", _
Title:="Delete row"

If ListBox1.ListIndex <> -1 Then
ListBox1.RemoveItem (ListBox1.ListIndex)
End If
 
Sub test()
Dim MyCell As Range
Dim RowNo As Long
Set MyCell = Application.InputBox(Prompt:="Click on the row you wish to
delete", _
Title:="Delete row", Type:=8)
RowNo = MyCell.Row
Rows(RowNo).Delete
End Sub

Mike F
 
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)
 
A simplified way to do it. Right click sheet tab>view code>copy paste this.
Now, when ANY cell is double clicked the row will be deleted.
You may want to restrict to a certain column. If so, uncomment the IF line
and change.

Private Sub Worksheet_BeforeDoubleClick _
(ByVal Target As Range, Cancel As Boolean)
'if target.column <> 2 then exit sub
Rows(Target.Row).Delete
End Sub
 
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
 
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
 
Working perfectly - now I have to study the code to understand the
process. Thanks a lot for your time

Beeo
 
Back
Top