# slow code

#### steve hill

##### Board Regular
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

Steve

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
This may be faster

Code:
``````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``````

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.

Code:
``````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``````

This may be faster

Code:
``````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

It is looking for blank columns. Perhaps try

Rich (BB code):
``````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 is looking for blank columns. Perhaps try

Rich (BB code):
``````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

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.

Replies
4
Views
193
Replies
7
Views
192
Replies
21
Views
319
Replies
7
Views
189
Replies
0
Views
156

1,203,070
Messages
6,053,365
Members
444,657
Latest member
jessejames1of3

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back