I am beginning to learn macros and went through the tutorial on-line about
how to do a Loop macro. I have successfully run the macro, however, I want
to limit it. Right now the macro looks at columns C, J, K, and L and if the
data matches, it deletes or highlights the duplicate entry. However, this
does not take into account blank rows. Right now I have data in the first 18
rows and when I run the macro, it highlights not only my duplicate test
record, but also from rows 19 - infinity (or what seems like infinity).

How do I tell the macro to only look for duplicates in rows with text data
and to ignore the blank rows with my formulas in them? Worksheet (2) that
I'm running the macro on is being fed the data from Worksheet (1) with the
use of formulas.

Please help,
Thanks so much,
Sharon

p.s. here's the code I got from the on-line tutorial that I'm using:
Sub MultipleEntryDeletion()

'Start at the currently selected cell
x = ActiveCell.Row
y = x + 1

'Outside loop
Do While Cells(x, 3).Value < ""
'Inside loop
Do While Cells(y, 3).Value < ""
'Test for duplication:
'If the values of the third column (C) and the fifth column (E)
match in two rows (this part of the code I edited)
'delete the second row of the pair, otherwise go to the next row
until the end
If (Cells(x, 3).Value = Cells(y, 3).Value) And (Cells(x,
10).Value = Cells(y, 10).Value) And (Cells(x, 11).Value = Cells(y, 11).Value)
And (Cells(x, 12).Value = Cells(y, 12).Value) And (Cells(x, 13).Value =
Cells(y, 13).Value) Then

'FOR DUPLICATE DELETION: Uncommment the following line by
removing the apostrophe
'Cells(y, 3).EntireRow.Delete

'Shade the entire row green if it's a duplicate
'FOR DUPLICATE DELETION: Make the following line a comment
by adding an apostrophe
Cells(y, 3).EntireRow.Interior.ColorIndex = 4
Else

'FOR DUPLICATE DELETION: Uncomment the following line by
removing the apostrophe
'y = y + 1
End If

'FOR DUPLICATE DELETION: Make the following line a comment by
adding an apostrophe
y = y + 1
Loop
'increase the value of x by 1 to move the loop starting point to the
next row
x = x + 1
'reset y so it starts at the next row
y = x + 1
Loop

End Sub

Hi
see:
http://www.cpearson.com/excel/deleti...eDuplicateRows

--
Regards
Frank Kabel
Frankfurt, Germany

"Sharon" schrieb im Newsbeitrag
...
I am beginning to learn macros and went through the tutorial on-line
about
how to do a Loop macro. I have successfully run the macro, however,
I want
to limit it. Right now the macro looks at columns C, J, K, and L and
if the
data matches, it deletes or highlights the duplicate entry. However,
this
does not take into account blank rows. Right now I have data in the
first 18
rows and when I run the macro, it highlights not only my duplicate
test
record, but also from rows 19 - infinity (or what seems like
infinity).

How do I tell the macro to only look for duplicates in rows with text
data
and to ignore the blank rows with my formulas in them? Worksheet (2)
that
I'm running the macro on is being fed the data from Worksheet (1)
with the
use of formulas.

Please help,
Thanks so much,
Sharon

p.s. here's the code I got from the on-line tutorial that I'm using:
Sub MultipleEntryDeletion()

'Start at the currently selected cell
x = ActiveCell.Row
y = x + 1

'Outside loop
Do While Cells(x, 3).Value < ""
'Inside loop
Do While Cells(y, 3).Value < ""
'Test for duplication:
'If the values of the third column (C) and the fifth
column (E)
match in two rows (this part of the code I edited)
'delete the second row of the pair, otherwise go to the
next row
until the end
If (Cells(x, 3).Value = Cells(y, 3).Value) And (Cells(x,
10).Value = Cells(y, 10).Value) And (Cells(x, 11).Value = Cells(y,
11).Value)
And (Cells(x, 12).Value = Cells(y, 12).Value) And (Cells(x, 13).Value
=
Cells(y, 13).Value) Then

'FOR DUPLICATE DELETION: Uncommment the following
line by
removing the apostrophe
'Cells(y, 3).EntireRow.Delete

'Shade the entire row green if it's a duplicate
'FOR DUPLICATE DELETION: Make the following line a
comment
by adding an apostrophe
Cells(y, 3).EntireRow.Interior.ColorIndex = 4
Else

'FOR DUPLICATE DELETION: Uncomment the following line
by
removing the apostrophe
'y = y + 1
End If

'FOR DUPLICATE DELETION: Make the following line a
comment by
adding an apostrophe
y = y + 1
Loop
'increase the value of x by 1 to move the loop starting point
to the
next row
x = x + 1
'reset y so it starts at the next row
y = x + 1
Loop

End Sub

Thanks for the resource. I read through the suggestions but the code they
provide is for a range of cells. In my workbook I am using rows 1 - 499, but
I don't necessarily want the macro to run until row 499. I want it to stop
when it reaches the last row with text data in it. For example, right now I
have data in rows 1 - 18 but nothing in rows 19 - 499.

Any ideas?

Thanks so much for any help,

Sharon

"Frank Kabel" wrote:

Hi
see:
http://www.cpearson.com/excel/deleti...eDuplicateRows

--
Regards
Frank Kabel
Frankfurt, Germany

"Sharon" schrieb im Newsbeitrag
...
I am beginning to learn macros and went through the tutorial on-line
about
how to do a Loop macro. I have successfully run the macro, however,
I want
to limit it. Right now the macro looks at columns C, J, K, and L and
if the
data matches, it deletes or highlights the duplicate entry. However,
this
does not take into account blank rows. Right now I have data in the
first 18
rows and when I run the macro, it highlights not only my duplicate
test
record, but also from rows 19 - infinity (or what seems like
infinity).

How do I tell the macro to only look for duplicates in rows with text
data
and to ignore the blank rows with my formulas in them? Worksheet (2)
that
I'm running the macro on is being fed the data from Worksheet (1)
with the
use of formulas.

Please help,
Thanks so much,
Sharon

p.s. here's the code I got from the on-line tutorial that I'm using:
Sub MultipleEntryDeletion()

'Start at the currently selected cell
x = ActiveCell.Row
y = x + 1

'Outside loop
Do While Cells(x, 3).Value < ""
'Inside loop
Do While Cells(y, 3).Value < ""
'Test for duplication:
'If the values of the third column (C) and the fifth
column (E)
match in two rows (this part of the code I edited)
'delete the second row of the pair, otherwise go to the
next row
until the end
If (Cells(x, 3).Value = Cells(y, 3).Value) And (Cells(x,
10).Value = Cells(y, 10).Value) And (Cells(x, 11).Value = Cells(y,
11).Value)
And (Cells(x, 12).Value = Cells(y, 12).Value) And (Cells(x, 13).Value
=
Cells(y, 13).Value) Then

'FOR DUPLICATE DELETION: Uncommment the following
line by
removing the apostrophe
'Cells(y, 3).EntireRow.Delete

'Shade the entire row green if it's a duplicate
'FOR DUPLICATE DELETION: Make the following line a
comment
by adding an apostrophe
Cells(y, 3).EntireRow.Interior.ColorIndex = 4
Else

'FOR DUPLICATE DELETION: Uncomment the following line
by
removing the apostrophe
'y = y + 1
End If

'FOR DUPLICATE DELETION: Make the following line a
comment by
adding an apostrophe
y = y + 1
Loop
'increase the value of x by 1 to move the loop starting point
to the
next row
x = x + 1
'reset y so it starts at the next row
y = x + 1
Loop

End Sub

Presently your code is stop executing duplicates if a row as no data a
column c, so I think it is stopping when no data is found.

What do you mean by last row?. Do you have blank rows between rows,
blank row represent last row?

--
anilsolipura
-----------------------------------------------------------------------
anilsolipuram's Profile: http://www.excelforum.com/member.php...fo&userid=1627
View this thread: http://www.excelforum.com/showthread.php?threadid=27794

hmmm....i hope i can explain this:
i have columns A - AG in worksheet 2. in worksheet 2, cells A1:F499 have a
formula in them to pull the data from worksheet 1 and display it.
as time goes by, a client will paste info into worksheet 1, columns A:F.
currently i have data up until row 18 without any blank lines. but until
the client adds more data later into worksheet 1, rows 19 - 499 are blank.
when i run the macro as it is right now, it searches for duplicate entries
and highlights them green. however, since rows 19 - 499 have formulas in
them it turns all of those rows green too. and i don't want it to do that, i
only want to highlight the duplicate rows with actual data in them.
is this possible? should i just run the macro on worksheet 1 and delete (or
clear contents) or highlight from there instead of worksheet 2 where i have
formulas?
once i can (if i can) get the highlighting to work, i'll change to code to
actually delete the dup's.

thanks for your help, i really appreciate it,
sharon

"anilsolipuram" wrote:


Presently your code is stop executing duplicates if a row as no data at
column c, so I think it is stopping when no data is found.

What do you mean by last row?. Do you have blank rows between rows, a
blank row represent last row?.


--
anilsolipuram
------------------------------------------------------------------------
anilsolipuram's Profile: http://www.excelforum.com/member.php...o&userid=16271
View this thread: http://www.excelforum.com/showthread...hreadid=277948