PDA

View Full Version : Solved: Deleting row from another worksheet based on user input criteria?



jvenning
11-25-2008, 06:00 PM
Hi everyone. Here's my situation.

I have a sheet that contains details of car parts - each part per row. The first column has the part no. of the part in. I would like to have a delete function that when the user clicks on a button, a prompt would show asking for the part no. of the row to be deleted.

I have the following code but I get an 'Identifier under cursor is not recognized' error when running it:


Private Sub CommandButton1_Click()
Set Sheet = book.Worksheets("Parts")
XL.Visible = True
Sheet.Activate
strToFind = InputBox("Enter Part No.")
Set Rng = Sheet.Range("A:A")
Set toDel = Rng.Find(strToFind)
If Not toDel Is Nothing Then
Sheet.Rows(toDel.Row).Delete
Else
Wscript.Echo "Selection not valid"
End If
End Sub


I have tried many codes before but the script would be launched from a different worksheet - most of the codes I have used before contained 'Activeworksheet' thus wont delete the data on a seperate worksheet.

Any help is much appreciated.

Regards,
J.Venning

GTO
11-25-2008, 07:42 PM
Greetings J,

Hey, I see that you just joined today! Let me be the first to say "Welcome", as well as relay that you will 'meet' plenty of exceptional folks here :-)

Now as to your code, I suspect that you may have copied it from an example? I get the following errors, and wonder whether maybe at least a bit of it was an example for .vbs, due to the Wscript.Echo.

'// Error: Object required (What is "book"?) //
'Set Sheet = book.Worksheets("Sheet1")
Set Sheet = Worksheets("Sheet1")

'// Error: Object required (What is "XL"?) //
'XL.Visible = True

Sheet.Activate
strToFind = InputBox("Enter Part No.")

Set Rng = Sheet.Range("A:A")
Set toDel = Rng.Find(strToFind)

If Not toDel Is Nothing Then
Sheet.Rows(toDel.Row).Delete
Else

'// Error: Object required ("Wscript.Echo"? You are writing the code in Excel, correct?)//
'Wscript.Echo "Selection not valid"
End If
End Sub

Anyways, not really important as to where it came from. Here J, the following should work.

Sub Button1_Click()
Dim _
strToFind As String, _
Rng As Range, _
toDel As Range

strToFind = InputBox("Enter Part No.")

Set Rng = ThisWorkbook.Worksheets("Parts").Range("A:A")

Set toDel = Rng.Find(strToFind)

If Not toDel Is Nothing Then
Rng.Parent.Rows(toDel.Row).Delete
Else
MsgBox "Selection is not valid", vbCritical, ""
End If

End Sub

Hope that helps, and again, welcome:thumb

Mark

jvenning
11-30-2008, 12:43 PM
Thank you for your reply! All working fine now :)

Regards

n8Mills
11-30-2008, 01:33 PM
J,

If you would, please mark your discussions as "Solved" once you think it applies. This will help your helpers from further trying to resolve an issue you don't need help with anymore.

You can get at it by finding the "Thread Tools http://www.vbaexpress.com/forum/images/misc/menu_open.gif" link near the top.