slow code

[steve hill]

Hi I am using the code below to delete lines that have no data in columns F to O
as this starts from the possible last row excel will allow it takes a long time to run. Have tried changing the range from A65536 to A10000 but this seemed to lockup the sheet. is there a quicker way of doing this. the sheet has about 7000 lines at this time and grows by upto 500 per month

Sub DELETE_ROWS()
'
For MY_ROWS = Range("A65536").End(xlUp).Row To 2 Step -1
MY_CELL = Range("F" & MY_ROWS).Value & Range("G" & MY_ROWS).Value & Range("H" & MY_ROWS).Value _
& Range("I" & MY_ROWS).Value & Range("J" & MY_ROWS).Value & Range("K" & MY_ROWS).Value _
& Range("L" & MY_ROWS).Value & Range("M" & MY_ROWS).Value & Range("N" & MY_ROWS).Value _
& Range("O" & MY_ROWS).Value
If MY_CELL = "" Then Rows(MY_ROWS).Delete
Next MY_ROWS
'
End Sub

thanks for your time

Steve

 

[VoG]

This may be faster

Sub delbl()
Dim LR As Long, i As Long
LR = Range("F" & Rows.Count).End(xlUp).Row
For i = LR To 1 Step -1
    If WorksheetFunction.CountA(Range("F" & i & ":O" & i)) = 0 Then Rows(i).Delete
Next i
End Sub

 

[Jubjab]

This is maybe stating the obvious, but remember to turn screenupdating off, since deleting rows takes a lot of time if you have it turned on.

Sub delbl()
Dim LR As Long, i As Long
Application.screenupdating = false
LR = Range("F" & Rows.Count).End(xlUp).Row
For i = LR To 1 Step -1
    If WorksheetFunction.CountA(Range("F" & i & ":O" & i)) = 0 Then Rows(i).Delete
Next i
Application.screenupdating = true
End Sub

 

[steve hill]

This may be faster

Sub delbl()
Dim LR As Long, i As Long
LR = Range("F" & Rows.Count).End(xlUp).Row
For i = LR To 1 Step -1
    If WorksheetFunction.CountA(Range("F" & i & ":O" & i)) = 0 Then Rows(i).Delete
Next i
End Sub

nothing seems to happen is it looking for zeros or blank columns

steve

 

[VoG]

It is looking for blank columns. Perhaps try

Sub delbl()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = LR To 1 Step -1
    If WorksheetFunction.CountA(Range("F" & i & ":O" & i)) = 0 Then Rows(i).Delete
Next i
End Sub

 

[steve hill]

It is looking for blank columns. Perhaps try

Sub delbl()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = LR To 1 Step -1
    If WorksheetFunction.CountA(Range("F" & i & ":O" & i)) = 0 Then Rows(i).Delete
Next i
End Sub

it ran this time deleted approx 800 rows but left about 5000 plank ones behind

steev

 

[VoG]

it ran this time deleted approx 800 rows but left about 5000 plank ones behind

steev

That suggests that those rows are not actually blank - maybe they contain spaces.