CODEWITHSUNDEEP
vbaexcel
sai krishna
sai krishna

Reputation: 33

Need Alternate Code for For Loop

I'm using below code in vba but it taking too much time to run. Report have 8 sheets and 450+ rows should be check in each sheet.

Sub forloop()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual
lr = Cells(Rows.Count, 3).End(xlUp).Row - 1
For s = 1 To Sheets.Count
    For x = lr To 1 Step -1
        If Cells(x, 2) <> "" Then
        Cells(x, 2).EntireRow.Delete
    Next x
Next s
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.Calculation = xlCalculationAutomatic
End Sub

Could you please suggest me any alternate method to run fast.

Upvotes: 0

Views: 31

Answers (1)

Tim Williams
Tim Williams

Reputation: 166316

dim wb as workbook, sht as worksheet, lr as long, r as long

set wb = workbook.open(wbPathHere)

for each sht in wb.worksheets
    lr = sht.cells(sht.rows.count, 3).End(xlUp).row - 1
    for r = lr to 1 step-1
        if sht.cells(r, 2) <> "" Then sht.cells(r, 2).entirerow.delete
    next r    
next sht

Upvotes: 1

Related Questions