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

[jvenning]

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:

[VBA]
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
[/VBA]

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]

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.

[vba]'// 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[/vba]

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

[vba]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[/vba]

Hope that helps, and again, welcome

Mark

[jvenning]

Thank you for your reply! All working fine now

Regards

[n8Mills]

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 " link near the top.