Help with codes
 

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

Help with codes
 

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

"Tom" wrote:

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

Help with codes
 

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 wrote:

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

--

Dave Peterson

Help with codes
 

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

Regards,
Tom

"Tom" wrote in message
.. .
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

Help with codes
 

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

"Joel" wrote in message
...
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

"Tom" wrote:

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