I am looking for a Formula that can delete all of the duplicates but keep one
record. I want the entire row to be deleted if it is a duplicate, and I do
not want to use a filter. I need this function for a Macro and the Macro
does not like using a filter.
For example,
1
2
2
3
1
4
2
3

Should only show at the end of the formula.
1
2
3
4

Any advice would be appreciated!

Posting your code might help us help you alittle more but this might do.

Sub RemoveDuplicates()
Dim whatColumn As Integer
Dim lr As Long
whatColumn = 1 '1 = A
lr = ActiveSheet.Cells(Rows.Count, whatColumn).End(xlUp).Row
ActiveSheet.Range("$A$1:$E$" & lr)_
.RemoveDuplicates Columns:=whatColumn, Header:=xlYes
End Sub

"Lost in Excel" wrote:

I am looking for a Formula that can delete all of the duplicates but keep one
record. I want the entire row to be deleted if it is a duplicate, and I do
not want to use a filter. I need this function for a Macro and the Macro
does not like using a filter.
For example,
1
2
2
3
1
4
2
3

Should only show at the end of the formula.
1
2
3
4

Any advice would be appreciated!

Hi,

Macros have no problem with filters. However, here is code to remove
duplicated:

in this case my duplicate data is in A1:A1111

Sub RemoveDuplicates()
Dim Bottom As Long
Columns("B:B").Insert
Bottom = [A1].End(xlDown).Row
Range("B1:B" & Bottom).Select
Selection = "=1/(COUNTIF(R1C[-1]:RC[-1],RC[-1])1)"
Selection.SpecialCells(xlCellTypeFormulas, 1).EntireRow.Delete
Columns("B:B").Delete
End Sub

If this helps, please click the Yes button.

Cheers,
Shane Devenshire

"Lost in Excel" wrote:

I am looking for a Formula that can delete all of the duplicates but keep one
record. I want the entire row to be deleted if it is a duplicate, and I do
not want to use a filter. I need this function for a Macro and the Macro
does not like using a filter.
For example,
1
2
2
3
1
4
2
3

Should only show at the end of the formula.
1
2
3
4

Any advice would be appreciated!

"Lost in Excel" wrote:

I am looking for a Formula that can delete all of the duplicates but keep one
record. I want the entire row to be deleted if it is a duplicate, and I do
not want to use a filter. I need this function for a Macro and the Macro
does not like using a filter.
For example,
1
2
2
3
1
4
2
3

Should only show at the end of the formula.
1
2
3
4

Any advice would be appreciated!



IF(A1=A2,"duplicate","ok")

Filter the Column.Select Duplicate and Delete the Row

Hardeep kanwar

The formula I have using is =IF(COUNTIF($C$9:C9,C9)1,"a",1). Then I go
through and filter the "a". I delete all of the "a" and then remove the
filter. When I recorded this in the Macro it did not run properly on a new
spreadsheet. Any ideas why? Is there something I need to do differently?
I'm not familar with Visual Basic so I'm not sure what I did wrong. Thanks.

"Lost in Excel" wrote:

I am looking for a Formula that can delete all of the duplicates but keep one
record. I want the entire row to be deleted if it is a duplicate, and I do
not want to use a filter. I need this function for a Macro and the Macro
does not like using a filter.
For example,
1
2
2
3
1
4
2
3

Should only show at the end of the formula.
1
2
3
4

Any advice would be appreciated!