Best way to write this??

[spcalan]

I am trying to delete the entire row if the column J is greater than 1.
I know there are million ways to writethis.

Any suggestions?


Sub delete_columnj()
x = 1
Do Until x > 10000
If Sheets("sheet1").Cells(x, 10) > 1 Then
Rows(x).Select
Selection.Delete Shift:=xlUp
Else
x = x + 1
End If

Loop

End Sub

 

[Jonmo1]

3 improvements...

1. Don't use select, that's very slow
2. Go backwards, from the bottom up..
3. Turn off ScreenUpdating and Events to speed things up..

Sub delete_columnj()
 
'Determine the last used row in column J
LR = Sheets("sheet1").Cells(Rows.Count,"J").End(xlup).row
 
'turn off screenupdating and events (greatly improve speed)
Application.EnableEvents = False
Application.Screenupdating = False
 
'Loop from the bottom (LR) to 1 going backwards..
For x = LR to 1 Step - 1
    If Sheets("sheet1").Cells(x,"J") > 1 Then
        Sheets("sheet1").Rows(x).EntireRow.Delete
    End If
Next x
 
'turn Back On screenupdating and events
Application.EnableEvents = True
Application.Screenupdating = True
 
End Sub

Hope this helps..

 

[spcalan]

3 improvements...

1. Don't use select, that's very slow
2. Go backwards, from the bottom up..
3. Turn off ScreenUpdating and Events to speed things up..

Sub delete_columnj()
 
'Determine the last used row in column J
LR = Sheets("sheet1").Cells(Rows.Count,"J").End(xlup).row
 
'turn off screenupdating and events (greatly improve speed)
Application.EnableEvents = False
Application.Screenupdating = False
 
'Loop from the bottom (LR) to 1 going backwards..
For x = LR to 1 Step - 1
    If Sheets("sheet1").Cells(x,"J") > 1 Then
        Sheets("sheet1").Rows(x).EntireRow.Delete
    End If
Next x
 
'turn Back On screenupdating and events
Application.EnableEvents = True
Application.Screenupdating = True
 
End Sub

Hope this helps..

Thank you for the help, and expecially the quotes on what exactly each line means.

Best so far.