Deleteing Duplicate Rows???

[James]

Hi Experts

I have a excel sheet of over 5000 rows of data (I get a
differnt one every week). Is their a way to delete a
duplicate, triplicate etc (it could have unlimited number
of same data) based on Value in Column A. i.e Value in
Column A are determining factor in deciding which row
should be considered duplicate.
I would ike to achieve this totaly through VBA in Excel
2002. I would appricate in your help if its possible.

Thanks a lot
James

 

[Frank Kabel]

Hi
see: http://www.cpearson.com/excel/deleting.htm#DeleteDuplicateRows

 

[Bob Phillips]

James,

Here is a very quick way of doing it

Sub filterData()
Dim cRows As Long
Dim rng As Range
Dim testFormula As String

Application.ScreenUpdating = False

Columns("G:G").Insert
Rows(1).Insert
Cells(1, "G").Value = "Temp"

With ActiveSheet
cRows = .Cells(.Rows.Count, "F").End(xlUp).Row
testFormula = "=IF(COUNTIF(F$2:F2,F2)>1,""Y"" ,"""")"
'create a test formula
.Cells(2, "G").Formula = testFormula
'copy the formula down all rows
.Cells(2, "G").AutoFill Destination:=.Range(.Cells(2, "G"), _
.Cells(cRows, "G"))
Set rng = .Range("G:G")
rng.AutoFilter Field:=1, Criteria1:="Y"

End With

rng.SpecialCells(xlCellTypeVisible).EntireRow.Delete

Columns("G:G").Delete

Application.ScreenUpdating = True

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[CPower]

Hi guys,

is there a way of deleting rows, on the basis that a cell in th
particular rows, contains a word like "closed" or "suspended"

e.g rows c4 to c450 contains a, b, c, d, e and i want to delete all th
rows containing "a" in that particular column using macros.

can you please help me out??

Thanks Cathal

 

[Frank Kabel]

Hi
try the following macro
Sub delete_rows()
Dim RowNdx As Long
Dim LastRow As Long
Application.ScreenUpdating = False
LastRow = ActiveSheet.Cells(Rows.Count, "C").End(xlUp).row
For RowNdx = LastRow To 1 Step -1
with Cells(RowNdx, "C")
if .value = "a" then
Rows(RowNdx).Delete
End If
end with
Next RowNdx
Application.ScreenUpdating = True
End Sub

 

[Tom Ogilvy]

Sub Tester1()
Dim rng as Range, rng1 as Range
Set rng = Range(Range("C4"),Cells(rows.count,3).End(xlup))
rng.Replace What:="a", Replacement:="=Na()", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False
On Error Resume Next
set rng1 = rng.SpecialCells(xlCellTypeFormulas, xlErrors)
On Error goto 0
If not rng1 is nothing then
rng1.EntireRow.Delete
End if
End Sub

 

[Bob Phillips]

Sub filterData()
Dim cRows As Long
Dim rng As Range
Dim testFormula As String

Application.ScreenUpdating = False

Columns("G:G").Insert
Rows(1).Insert
Cells(1, "G").Value = "Temp"

With ActiveSheet
cRows = .Cells(.Rows.Count, "F").End(xlUp).Row
testFormula = "=IF(F2=""a"",""Y"" ,"""")"
'create a test formula
.Cells(2, "G").Formula = testFormula
'copy the formula down all rows
.Cells(2, "G").AutoFill Destination:=.Range(.Cells(2, "G"), _
.Cells(cRows, "G"))
Set rng = .Range("G:G")
rng.AutoFilter Field:=1, Criteria1:="Y"

End With

rng.SpecialCells(xlCellTypeVisible).EntireRow.Delete

Columns("G:G").Delete

Application.ScreenUpdating = True

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[CPower]

Thanks a million for the help guys, this has worked for me

 

[CPower]

I am now gettting an error when i put in multiple if statements, coul
someone please let me know where i am going wrong??
i don't want the code to be dependent on the word in inverted comms.

here is where i am getting the error messgae.....

Dim RowNdx As Long
Application.ScreenUpdating = False
LastRow = ActiveSheet.Cells(Rows.Count, "F").End(xlUp).Row
For RowNdx = LastRow To 1 Step -1
With Cells(RowNdx, "F")
If .Value = "Prioritization" Then
Rows(RowNdx).Delete
End If
If .Value = "Dev Analysis" Then ........on this line here???
Rows(RowNdx).Delete
End If
If .Value = "Suspend" Then
Rows(RowNdx).Delete
End If
If .Value = "Escalated" Then
Rows(RowNdx).Delete
End If

Thanks,
Catha

 

[Bob Phillips]

Cathal,

Because you delete a row, the With statement has no object. Try this version

Dim RowNdx As Long
Application.ScreenUpdating = False
LastRow = ActiveSheet.Cells(Rows.Count, "F").End(xlUp).Row
For RowNdx = LastRow To 1 Step -1
With Cells(RowNdx, "F")
If .Value = "Prioritization" Then
Rows(RowNdx).Delete
ElseIf .Value = "Dev Analysis" Then
Rows(RowNdx).Delete
ElseIf .Value = "Suspend" Then
Rows(RowNdx).Delete
ElseIf .Value = "Escalated" Then
Rows(RowNdx).Delete
End If
End With
Next RowNdx

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[CPower]

Cheers bob, perfect,

Thanks,
Cathal

 

[CPower]

Is there any chance you get me some code to delete a row on the basi
that a cell in one of its columns contains text. My reason for this i
that, the row is relavant if the problem report column is empty, wher
as if it has some text in it like CYUFR1234 it must be deleted from th
spreadsheet.

Thanks again,
Cathal

 

[Bob Phillips]

Dim RowNdx As Long
Application.ScreenUpdating = False
LastRow = ActiveSheet.Cells(Rows.Count, "F").End(xlUp).Row
For RowNdx = LastRow To 1 Step -1
If Cells(RowNdx,"F").Value <> "" Then
Cells(RowNdx,"F").Entirerow.Delete
End If
Next RowNdx


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[CPower]

Thanks bob