How do I make the following "For" VBA faster?

[Wamhoi]

Hi, I'm working with the following VBA to remove text/blank but its very slow when working with larger data files. Is there a way to make it faster?

For i = Lastline To 1 Step -1
If Not IsNumeric(Cells(i, 1).Value) Then Rows(i).Delete
If Cells(i, 1) = "" Then Rows(i).Delete
Next i

 

[VoG]

Hi. Try

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
For i = Lastline To 1 Step -1
    If Not IsNumeric(Cells(i, 1).Value) Then Rows(i).Delete
    If Cells(i, 1) = "" Then Rows(i).Delete
Next i
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

 

[MrKowz]

Welcome to the forums!

Turn off screenupdating and calculations:

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For i = Lastline To 1 Step -1
    If Not IsNumeric(Cells(i, 1).Value) Or Cells(i,1) = "" Then Rows(i).Delete
Next i 
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

 

[Wamhoi]

Macro runs twice as fast. Thanks!

 

[Richard Schollar]

You might like to try this approach:

Dim rng As Range
Dim xlCalc As xlCalculation

With Application
  xlCalc = .Calculation
  .Calculation = xlCalculationManual
 End With

Set rng = Range("A1:A" & Cells(Rows.Count).End(xlUp).Row)

rng.Autofilter Field:=1,Criteria1:="*", Operator:=xlOr, Criteria2:="="

rng.Offset(1).EntireRow.Delete

rng.Autofilter

Application.Calculation = xlCalc