blackie42
10-04-2007, 06:25 AM
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
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