Deleting rows based on font

[Yarroll]

Hello,

What's the best way to delete rows on the basis of cell's font?
I'm running the following macro, but with large workbooks it takes forever
to complete :-((

Thanks. Best Regards
Yarroll

Dim k As Long, howm As Long
howm = WorksheetFunction.CountA(ActiveSheet.Range("N:N"))

For k = howm To 2 Step -1
If Cells(k, 12).Font.Bold = False Then
Rows(k).Delete
End If
Next

End sub

 

[Frank Kabel]

Hi
try if the following is a little bit faster:

Dim k As Long, howm As Long
howm = WorksheetFunction.CountA(ActiveSheet.Range("N:N"))
application.screenupdating = false
For k = howm To 2 Step -1
If Cells(k, 12).Font.Bold = False Then
Rows(k).Delete
End If
Next
application.screenupdating = true
End sub

 

[Nikos Yannacopoulos]

Yarroll,

There are two things you can do to improve execution:
1. Restrict the area by looking for bold from the last used cell up, instead
of the whole sheet:

Range("A1").Select
howm = ActiveCell.SpecialCells(xlLastCell).Row
For k = howm To 2 Step -1
If Cells(k, 12).Font.Bold = False Then Rows(k).Delete
Next

2. Disable screen updating during the action. Put this satement at the
beginning of the sub:
Application.ScreenUpdating = False
and another one to set it back to True at the end.

You should gain significant improvement.

HTH,
Nikos

 

[Yarroll]

Yarroll,

There are two things you can do to improve execution:

(snip)

Thanks guys. Best regards,
Yarroll