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

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.

--
Charles Chickering

"A good example is twice the value of good advice."


"Scott" wrote:

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

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"

--
HTH,
George



"Scott" <(E-Mail Removed)> wrote in message
news:916515c6-4c6c-4d5c-b261-(E-Mail Removed)...
> 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

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/xladvfi...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" <(E-Mail Removed)>
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