Solved: Delete same row from multiple sheets

[blackie42]

Hi
Bit of a beginner here - please be patient

I have a workbook with multiple sheets - all are named.

I have the same customer record in a number of the sheets. In fact the record could be in any or all of the sheets.(15 sheets)

What I'd like to do is create an input box (well probably a user form as I have other user forms involved) which asks for the customer number (unique) and then goes off searches all sheets and deletes that record(row) in each sheet.
I have got as far as this - which deletes the row in a sheet with 'me' but only once (and not in all sheets)

Sub delete_records()
Dim rNa As Range
Dim i As Integer

Set rNa = Range("A1")

Set rNa = Columns(1).Find(What:="me", After:=rNa, _
LookIn:=xlValues, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=True)
rNa.EntireRow.Delete


End Sub

Can anyone write me the extra bits i need - I think I ned to get 'me' to change to the value of the rcord from the input box and then set a loop to search each sheet in turn. Any help appreciated.
BTW the account record would always be in the 2nd column in each sheet
thanks

Jon

[Charlize]

I think this will do what you want (but only one occurence per sheet).Sub delete_records()
Dim rNa As Range
Dim i As Integer
Dim vSearch As String
Dim ws As Worksheet
vSearch = Application.InputBox("Give cust.no", "Customer ?")
For Each ws In ThisWorkbook.Worksheets
Set rNa = ws.Cells.Find(What:=vSearch, _
After:=Range("A1"), LookIn:=xlValues, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=True)
rNa.EntireRow.Delete
Next ws
End Sub

[blackie42]

thanks a lot - just one more thing how to code the 'cancel' button and if 'string' is null?

thanks

[blackie42]

After testing the code gets stuck on 'rNa.EntireRow.Delete'

This code works but I need to get in to input box and change the 'me' value.

Sub Del_rows()
Dim Wrkst As Worksheet
Dim lLoop As Long
Dim rStart As Range

On Error Resume Next
For Each Wrkst In Worksheets
With Wrkst.UsedRange
Set rStart = .Cells(1, 1)
For lLoop = 1 To WorksheetFunction.CountIf(.Cells, "me")
Set rStart = .Find(What:="me", After:=rStart, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows)
rStart.EntireRow.Delete
Next lLoop
End With
Next Wrkst
End Sub

Any ideas please

[blackie42]

Ok think I got it working by adding

vSearch = InputBox("Give cust.no", "Customer ?")

and then replacing "me" with vsearch

thanks

[Bob Phillips]

See if this works



Sub Del_rows()
Dim Wrkst As Worksheet
Dim lLoop As Long
Dim rStart As Range

On Error Resume Next
For Each Wrkst In Worksheets
With Wrkst.UsedRange
For lLoop = 1 To WorksheetFunction.CountIf(.Cells, "me")
Set rStart = .Find(What:="me", After:=.Cells(1, 1), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows)
rStart.EntireRow.Delete
Next lLoop
End With
Next Wrkst
End Sub

[blackie42]

Thanks - how do I mark this as solved? Seen it somewhere but can't find it now

thanks

[Bob Phillips]

Look for thr Thread Tools dropdown at the head of the first post.