Deleteing Duplicate Rows???

  • Thread starter Thread starter James
  • Start date Start date
J

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
 
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)
 
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
 
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
 
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
 
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)
 
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
 
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)
 
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
 
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)
 
Back
Top