PDA

View Full Version : Solved: deleting rows again



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

Norie
10-07-2007, 07:10 AM
Shouldn't it be this?

If vsearch = vbCancel Then Exit Sub

Bob Phillips
10-07-2007, 07:40 AM
vSearch = InputBox("PLAN TO DELETE", "PLAN NUMBER ?", vbOKCancel)

If vSearch = "" Then Exit Sub

blackie42
10-07-2007, 09:00 AM
Yep - pretty simple really

thanks again

Lots to learn - long way to go

Jon