blackie42
10-07-2007, 06:55 AM
This is from an earlier Thread where the input box finds a particuar entry in multiple sheets and deletes (the first occurrence) in each sheet. It works if 'OK' is selected but if cancel hit deletes the first row in the first sheet.
Any idea where problem is?
Option Compare Text
Sub Del_rows()
Dim Wrkst As Worksheet
Dim lLoop As Long
Dim rStart As Range
Dim vsearch As String
vsearch = InputBox("PLAN TO DELETE", "PLAN NUMBER ?", vbOKCancel)
If vbCancel = True Then Exit Sub
On Error Resume Next
For Each Wrkst In Worksheets
With Wrkst.UsedRange
Set rStart = .Cells(1, 1)
For lLoop = 1 To WorksheetFunction.CountIf(.Cells, vsearch)
Set rStart = .Find(What:=vsearch, After:=rStart, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows)
rStart.EntireRow.Delete
Next lLoop
End With
Next Wrkst
End Sub
many thanks for your time
regards
Any idea where problem is?
Option Compare Text
Sub Del_rows()
Dim Wrkst As Worksheet
Dim lLoop As Long
Dim rStart As Range
Dim vsearch As String
vsearch = InputBox("PLAN TO DELETE", "PLAN NUMBER ?", vbOKCancel)
If vbCancel = True Then Exit Sub
On Error Resume Next
For Each Wrkst In Worksheets
With Wrkst.UsedRange
Set rStart = .Cells(1, 1)
For lLoop = 1 To WorksheetFunction.CountIf(.Cells, vsearch)
Set rStart = .Find(What:=vsearch, After:=rStart, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows)
rStart.EntireRow.Delete
Next lLoop
End With
Next Wrkst
End Sub
many thanks for your time
regards