Remove row in list based on user input
 

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

Remove row in list based on user input
 

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
--
jb


"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

Remove row in list based on user input
 

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

If ListBox1.ListIndex < -1 Then
ListBox1.RemoveItem (ListBox1.ListIndex)
End If

"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

Remove row in list based on user input
 

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
"Beeo" wrote in message
...
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

Remove row in list based on user input
 

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

Remove row in list based on user input
 

A simplified way to do it. Right click sheet tabview codecopy 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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Beeo" wrote in message
...
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

Remove row in list based on user input
 

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

Remove row in list based on user input
 

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

Remove row in list based on user input
 

Working perfectly - now I have to study the code to understand the
process. Thanks a lot for your time

Beeo


On 1 Mar, 16:57, Dave Peterson wrote:
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- Dölj citerad text -

- Visa citerad text -