Delete rows containing same value in two columns

[Scott]

Hello-

I have a spreadsheet that has 25-50 thousand rows. Columns D & E often
contain the same value, and I only want to keep the first instance
where the values are the same. I had code that would filter, but on
certain files my code will error out when trying to select only
visible cells to copy to another sheet. I later found out that it is
due to a limitation that there can only be 8100 or something non-
adjacent cells in the entire range. I figured that if I just deleted
the extra rows instead of filtering them, I could get around this.

A B C D E
115 115
115 115 Delete this
row
116 116
117 117
118 118
118 118 Delete this
row
118 118 Delete this
row

Thanks-

Scott

 

[Charles Chickering]

Scott, Try this Sub:

Sub DeleteDupColsAndRows()
Dim cnt As Long
Dim LRow As Long
Dim r As Range
LRow = Range("E" & Rows.Count).End(xlUp).Row
For cnt = LRow to 1 Step -1
Set r = Range("D" & cnt)
If r = r.Offset(,1) And r = r.Offset(-1) And r = r.Offset(-1, 1) Then
r.EntireRow.Delete
End If
Next cnt
End Sub

Note: I just typed this off the top of my head. It has not been tested for
errors.

 

[George Nicholson]

A couple of questions:
1) the values in D & E will always be identical within a single row (per
your example)?

2) Is the data in D & E guaranteed to be sorted ascending, as per your
example? (If not, can it be?)

3) Assuming 1 is "Yes", can you "save" the last occurance rather than the
first? (Row deletion is *much* more effiecient when done bottom-up, so if
the answer is no, can the sort order in #1 be reversed?)

4) What data are you copying? If you are just trying to get a list of unique
values from only D&E: Data>Filter>Advanced Filter and then check "Copy to
another location" and "Unique Values Only"

 

[Jim Cone]

As George pointed out, you can use advanced filter to create a
list of unique items.
If you want the list created on a separate sheet then Debra Dalgleish
shows how here... http://www.contextures.on.ca/xladvfilter01.html#ExtractWs

If your columns are discontinuous then it becomes a little more complicated.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
("XL Companion" will do it)



"Scott" <[email protected]>
wrote in message
Hello-
I have a spreadsheet that has 25-50 thousand rows. Columns D & E often
contain the same value, and I only want to keep the first instance
where the values are the same. I had code that would filter, but on
certain files my code will error out when trying to select only
visible cells to copy to another sheet. I later found out that it is
due to a limitation that there can only be 8100 or something non-
adjacent cells in the entire range. I figured that if I just deleted
the extra rows instead of filtering them, I could get around this.

A B C D E
115 115
115 115 Delete this row
116 116
117 117
118 118
118 118 Delete this row
118 118 Delete this row
Thanks-
Scott