Denblanc
04-01-2016, 06:35 PM
Good evening to everyone,
This code works but needs tweeking and not sure how to accomplish. My worksheet has 1300 rows and this code reduces it to 145. The two problems are:
1. After running I get the following msgbox: "Run-Time Error '1004' Delete method of Range class failed". {line it stops at highlighted below}
2. The time it takes to complete task is a good 2 minutes - would like to speed it up.
Any assistance would be appreciated.
Sub SortingCountry_Rows()
Dim lr As Long, r As Long, L As Long, i As Long
Dim BlackFound As Boolean
Application.ScreenUpdating = False
lr = Range("AD" & Rows.Count).End(xlUp).row
For r = lr To 3 Step -1
BlackFound = False
i = 0
With Range("AD" & r)
L = Len(.Value)
Do
i = i + 1
If .Characters(i, 1).Font.ColorIndex = 1 Then
BlackFound = True
End If
Loop Until BlackFound = True Or i = L
If BlackFound Then
Rows(r).Delete ' Error occurs here
End If
End With
Next r
Application.ScreenUpdating = True
End Sub
This code works but needs tweeking and not sure how to accomplish. My worksheet has 1300 rows and this code reduces it to 145. The two problems are:
1. After running I get the following msgbox: "Run-Time Error '1004' Delete method of Range class failed". {line it stops at highlighted below}
2. The time it takes to complete task is a good 2 minutes - would like to speed it up.
Any assistance would be appreciated.
Sub SortingCountry_Rows()
Dim lr As Long, r As Long, L As Long, i As Long
Dim BlackFound As Boolean
Application.ScreenUpdating = False
lr = Range("AD" & Rows.Count).End(xlUp).row
For r = lr To 3 Step -1
BlackFound = False
i = 0
With Range("AD" & r)
L = Len(.Value)
Do
i = i + 1
If .Characters(i, 1).Font.ColorIndex = 1 Then
BlackFound = True
End If
Loop Until BlackFound = True Or i = L
If BlackFound Then
Rows(r).Delete ' Error occurs here
End If
End With
Next r
Application.ScreenUpdating = True
End Sub