Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete Dups
Hello,
I have a spreadsheet that has a column that says "Duplicate?". I am trying to delete thse as there are over 6,000 rows I hate to do this manually. How can I delete the ones that say Dup in the cell either at once or doing a find and delete? Thanks! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete Dups
Try this small macro:
Sub stockwell() n = Cells(Rows.Count, "H").End(xlUp).Row For i = n To 1 Step -1 If Cells(i, "H").Value = "Duplicate?" Then Rows(i).Delete End If Next End Sub This code is for column H. Adapt to suit your needs. -- Gary''s Student - gsnu200809 "Stockwell43" wrote: Hello, I have a spreadsheet that has a column that says "Duplicate?". I am trying to delete thse as there are over 6,000 rows I hate to do this manually. How can I delete the ones that say Dup in the cell either at once or doing a find and delete? Thanks! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete Dups
use autofilter
On Oct 23, 9:18*pm, Stockwell43 wrote: Hello, I have a spreadsheet that has a column that says "Duplicate?". I am trying to delete thse as there are over 6,000 rows I hate to do this manually. How can I delete the ones that say Dup in the cell either at once or doing a find and delete? Thanks! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete Dups
Hi,
I assume this word 'Duplicate' is from a countif formula. If you are trying to delete duplicates from a column then I wouldn't start from there!! Select your column with duplicates in then Data|Filter|Advanced filter Select 'copy to another location' Check 'Unique records only' enter a cell address to copy them to OK and you now have a unique list to paste on top of the list with duplicates in Mike "Stockwell43" wrote: Hello, I have a spreadsheet that has a column that says "Duplicate?". I am trying to delete thse as there are over 6,000 rows I hate to do this manually. How can I delete the ones that say Dup in the cell either at once or doing a find and delete? Thanks! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete Dups
Hi,
I changed the H to E and placed it in a module and ran the macro and id gives me an error on this line: If Cells(i, "E").Value = "DUP" Then It says Type Mismatch. The column is E and word in that column is DUP. so if DUP is found in that column, I want the entire rows deleted. I may have explained that incorrectly the first time. Sorry about that. "Gary''s Student" wrote: Try this small macro: Sub stockwell() n = Cells(Rows.Count, "H").End(xlUp).Row For i = n To 1 Step -1 If Cells(i, "H").Value = "Duplicate?" Then Rows(i).Delete End If Next End Sub This code is for column H. Adapt to suit your needs. -- Gary''s Student - gsnu200809 "Stockwell43" wrote: Hello, I have a spreadsheet that has a column that says "Duplicate?". I am trying to delete thse as there are over 6,000 rows I hate to do this manually. How can I delete the ones that say Dup in the cell either at once or doing a find and delete? Thanks! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete Dups
You also need to change the H to E in the equation for n.
-- Gary''s Student - gsnu200809 "Stockwell43" wrote: Hi, I changed the H to E and placed it in a module and ran the macro and id gives me an error on this line: If Cells(i, "E").Value = "DUP" Then It says Type Mismatch. The column is E and word in that column is DUP. so if DUP is found in that column, I want the entire rows deleted. I may have explained that incorrectly the first time. Sorry about that. "Gary''s Student" wrote: Try this small macro: Sub stockwell() n = Cells(Rows.Count, "H").End(xlUp).Row For i = n To 1 Step -1 If Cells(i, "H").Value = "Duplicate?" Then Rows(i).Delete End If Next End Sub This code is for column H. Adapt to suit your needs. -- Gary''s Student - gsnu200809 "Stockwell43" wrote: Hello, I have a spreadsheet that has a column that says "Duplicate?". I am trying to delete thse as there are over 6,000 rows I hate to do this manually. How can I delete the ones that say Dup in the cell either at once or doing a find and delete? Thanks! |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete Dups
I did. I made sure I anything that was H I changed to E. However, I forgot to
mention there is an IF formula in the E column which is why I was unable to sort. I imported into Access, wrote a simple query and Exported back to excel and all is well. I work with access and didn't even think about that because I was so pre-occupied with trying to do it in excel. Thank you for your help and will save the code for spreadsheets with no formulas in the cells. "Gary''s Student" wrote: You also need to change the H to E in the equation for n. -- Gary''s Student - gsnu200809 "Stockwell43" wrote: Hi, I changed the H to E and placed it in a module and ran the macro and id gives me an error on this line: If Cells(i, "E").Value = "DUP" Then It says Type Mismatch. The column is E and word in that column is DUP. so if DUP is found in that column, I want the entire rows deleted. I may have explained that incorrectly the first time. Sorry about that. "Gary''s Student" wrote: Try this small macro: Sub stockwell() n = Cells(Rows.Count, "H").End(xlUp).Row For i = n To 1 Step -1 If Cells(i, "H").Value = "Duplicate?" Then Rows(i).Delete End If Next End Sub This code is for column H. Adapt to suit your needs. -- Gary''s Student - gsnu200809 "Stockwell43" wrote: Hello, I have a spreadsheet that has a column that says "Duplicate?". I am trying to delete thse as there are over 6,000 rows I hate to do this manually. How can I delete the ones that say Dup in the cell either at once or doing a find and delete? Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro - find dups & add | Excel Discussion (Misc queries) | |||
Comparing two columns for dups | Excel Discussion (Misc queries) | |||
database - looking for dups | Excel Discussion (Misc queries) | |||
Dups from 2 worksheets | Excel Worksheet Functions | |||
Identifying Dups in different ranges | Excel Worksheet Functions |