Help with codes

[Tom]

Hi,

My macro first sorts Column A which contains a string of text
alphabetically. It then compares the text down each consecutive row and
prints, "Repeated Entry" in Column B next to it if they are found. It then
sorts Column A:B alphabetically along Column B. At this point and to do a
repetition, For N = 1 to 100 is used. It then searches for the words
"Repeated Entry" and deletes that row. Then comes, Next N. Here is where I
need some help. If there are less than 100 "Repeated Entry" lines, I get the
message,
"Run time error '91': Object variable or With block variable not set". The
macro execution then stops. How can I error trap this so as to allow the
execution to continue on?

TIA
Tom

 

[Guest]

Use this type loop. Notice Rowcount doesn't get incremented when you delete
a row.

RowCount = 1
Do while cells(RowCount,"A") <> ""
if cells(RowCount,"B") = "Repeated Entry" then
Rows(RowCount).delete
else
RowCount = RowCount + 1
loop

 

[Dave Peterson]

You're code will be much simpler if you work from the bottom toward the top.

Option Explicit
Sub testme01()

Dim LastRow As Long
Dim FirstRow As Long
Dim iRow As Long

With Worksheets("sheet1")
FirstRow = 1
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
For iRow = LastRow To FirstRow Step -1
If LCase(.Cells(iRow, "B").Value) = LCase("repeated entry") Then
.Rows(iRow).Delete
End If
Next iRow
End With

End Sub

Instead of using 1 to N, I used 1 to the last row that was used in column B.

I could have used:

FirstRow = 1
LastRow = 100

 

[Tom]

My sincere thanks to you both, Joel and Dave. Your suggestions are much
appreciated.

Regards,
Tom

 

[Tom]

That last word "loop". Where should I put it for it to work properly?
Thanks!