Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete rows containing same value in two columns
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete rows containing same value in two columns
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete rows containing same value in two columns
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: DataFilterAdvanced Filter and then check "Copy to another location" and "Unique Values Only" -- HTH, George "Scott" 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete rows containing same value in two columns
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" 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete rows with multiple columns with 0 value | Excel Discussion (Misc queries) | |||
Delete rows with more or less than 7 columns | New Users to Excel | |||
Query to delete rows or columns | Excel Discussion (Misc queries) | |||
Delete rows that contain blank columns | Excel Worksheet Functions | |||
Delete columns or rows | Excel Worksheet Functions |