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!

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!

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!

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!

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!

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!

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!