Need Alternate Code for For Loop

Question

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.

Does your code do what you want? Seems like it only operates on the ActiveSheet... — Tim Williams
– Tim Williams, Commented Aug 4, 2016 at 19:31
@TimWilliams - i'll open required workbook and the code need to perform on that workbook — sai krishna
– sai krishna, Commented Aug 4, 2016 at 19:34
@TimWilliams - Yes, it need to run first sheet and then next sheet until last sheet — sai krishna
– sai krishna, Commented Aug 4, 2016 at 19:36
Could you just sort the sheet instead of deleting the entire row? — Ryan Wildry
– Ryan Wildry, Commented Aug 4, 2016 at 19:39
@saikrishna Well, as far as making it process all Sheets goes, you could change your instances of Cells(x, 2) to Sheets(s).Cells(x, 2) - but this won't help the speed. For that, you'll have to rework the row deletion, because deleting rows is time-consuming — Mikegrann
– Mikegrann, Commented Aug 4, 2016 at 19:44

Tim Williams · Accepted Answer · 2016-08-04 19:45:54Z

1

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

answered Aug 4, 2016 at 19:45

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Need Alternate Code for For Loop

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related