code help, need to delete both rows 2

[natedeia]

i can not figure out how to get this code to delete both rows that are the same instead of just the one.

Sub RemDupRows()

Dim lastcol As Integer, lastrow As Integer, i As Integer
Dim j As Integer, k As Integer, matchfound As Boolean
Dim rowMatch As Boolean
Application.ScreenUpdating = False
Range("A1").Select
Selection.End(xlToRight).Select
lastcol = ActiveCell.Column

Range("A1").Select
Selection.End(xlDown).Select
lastrow = ActiveCell.Row

For i = lastrow To 2 Step -1
matchfound = False
For j = i - 1 To 1 Step -1
rowMatch = True
For k = 1 To lastcol
If Cells(i, k) <> Cells(j, k) Then
rowMatch = False
k = lastcol
End If
Next k
If rowMatch Then
j = 1
matchfound = True
End If
Next j
If matchfound Then
Rows(i & ":" & i).EntireRow.Delete
End If
Next i
Range("A1").Select
End Sub

 

[TonyJollans]

Hi natedeia,

You only delete row i - you want to delete row j as well (but before you reset j to 1).

However, if you start deleting rows in the middle of your loops you will end up with problems and you might find it better to flag rows for deletion and come back and do the actual deletion later.

Try something like this:

:
:

Next k
If rowMatch then
  Cells(i, 256) = "DELETE"
  Cells(j, 256) = "DELETE"
  Exit For
end if
Next j
Next i
for i = lastrow to 1 step -1
  if cells(1,256) = "DELETE" then cells(i,256).entireRow.delete
next i

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[natedeia]

thanks tonyj, used your crafty fix but it seems to delete every row, i copied some rows twice and sorted with some other data that would not be dup and it all went. i could be doing something wrong, can you look at this code again? i placed your code in, like i said, may have done something wrong cause i can not read code fluently.


Sub RemDupRows()

Dim lastcol As Integer, lastrow As Integer, i As Integer
Dim j As Integer, k As Integer, matchfound As Boolean
Dim rowMatch As Boolean
Application.ScreenUpdating = False
Range("A1").Select
Selection.End(xlToRight).Select
lastcol = ActiveCell.Column

Range("A1").Select
Selection.End(xlDown).Select
lastrow = ActiveCell.Row

For i = lastrow To 2 Step -1
matchfound = False
For j = i - 1 To 1 Step -1
rowMatch = True
For k = 1 To lastcol
If Cells(i, k) <> Cells(j, k) Then
rowMatch = False
k = lastcol
End If

Next k
If rowMatch Then
Cells(i, 256) = "DELETE"
Cells(j, 256) = "DELETE"
Exit For
End If
Next j
Next i
For i = lastrow To 1 Step -1
If Cells(1, 256) = "DELETE" Then Cells(i, 256).EntireRow.Delete
Next i

Range("A1").Select
End Sub

 

[natedeia]

dont know what i did, but now getting 400 code error.

 

[TonyJollans]

Hi natedeia,

My typing mistake I think, sorry!

Try changing ..

If Cells(1, 256) = "DELETE" Then Cells(i, 256).EntireRow.Delete

to

If Cells([highlight yellow]i[/highlight], 256) = "DELETE" Then Cells(i, 256).EntireRow.Delete

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[natedeia]

dang Tonyj, these people over here think i am some kind of genius!
i just picked up reading material like a couple weeks ago! haha!
man, but thank you, i hope others can benefit from your efforts,

 

[Zathras]

natedeia, pardon me for asking, but what exactly are you hoping to accomplish?

Your original code looks like you want to delete duplicates (and triplicates, etc.) and leave exactly one copy of the data where a duplicate was found. But you said "need to delete both rows" If however, there were three instances of a row, deleting two at a time (as you find them) would leave the third behind. That is, if there are an odd number of matching rows (1, 3, 5, etc.) you would end up with one, but if there are an even number of matching rows (2, 4, etc.) you would obliterate them all.

What Tony's code will do is this: If there is only one copy of a data row, it is left alone, but if there are two or more than all traces of both (all) are removed.

In other words, if I'm reading this right, if you start with
[tt]
a
b
c
b
c
d
b
e
[/tt]
you will end up with
[tt]
a
d
e
[/tt]
with all three b's and both c's gone. I'm having a hard time picturing why you would want to do that.

 

[TonyJollans]

Good catch, Zathras.

I'm with you insofar as I don't know what natedeia wants to do, and my code doesn't do the same as it appears his/her original was trying to do, but I can certainly imagine wanting a list of only those items which are not duplicated (although this may not be the best way to do that).

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[natedeia]

what happens is that a query is run for specific information pertaining to various transactions that happen at stores, these are looked over by analysts, then they run it again , put the two together and they do not want to look at the same stuff, so once this Macro is run after sorting it will only show the transaction data for new transactions (in real time), so there should not be 3 or more dups unless there is some problem with the data being migrated from one DB to another (on occasion can happen), but yea, this works great for them to not waste any time figuring what to look at and not, keeps the spread sheets smaller too.

 

[Zathras]

natedeia, ok. I think I understand. Kind of like matter
and anti-matter. Poof, and both are gone.

Thanks for the explanation.

Tony, looks like you hit the nail on the head. (As usual )

 

[PHV]

a query is run
What about SELECT DISTINCT ... ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[PHV]

Oops, missed they do not want to look at the same stuff
So, what about something like this ?
SELECT fields list FROM tables list WHERE some conditions
GROUP BY fields list HAVING Count(*)=1

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244