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

Hi
see: http://www.cpearson.com/excel/deleti...eDuplicateRows

--
Regards
Frank Kabel
Frankfurt, Germany


James wrote:
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.Dele te

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)

"James" wrote in message
...
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

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

--
Message posted from http://www.ExcelForum.com

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

--
Regards
Frank Kabel
Frankfurt, Germany


Hi guys,

is there a way of deleting rows, on the basis that a cell in the
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
the rows containing "a" in that particular column using macros.

can you please help me out??

Thanks Cathal.


---
Message posted from http://www.ExcelForum.com/

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

--
Regards,
Tom Ogilvy

"CPower " wrote in message
...
Hi guys,

is there a way of deleting rows, on the basis that a cell in the
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 the
rows containing "a" in that particular column using macros.

can you please help me out??

Thanks Cathal.


---
Message posted from http://www.ExcelForum.com/

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.Dele te

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 " wrote in message
...
Hi guys,

is there a way of deleting rows, on the basis that a cell in the
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 the
rows containing "a" in that particular column using macros.

can you please help me out??

Thanks Cathal.


---
Message posted from http://www.ExcelForum.com/

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

--
Message posted from http://www.ExcelForum.com

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

--
Message posted from http://www.ExcelForum.com

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 " wrote in message
...
I am now gettting an error when i put in multiple if statements, could
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,
Cathal


---
Message posted from http://www.ExcelForum.com/

Cheers bob, perfect,

Thanks,
Cathal

--
Message posted from http://www.ExcelForum.com

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

--
Message posted from http://www.ExcelForum.com

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 " wrote in message
...
Is there any chance you get me some code to delete a row on the basis
that a cell in one of its columns contains text. My reason for this is
that, the row is relavant if the problem report column is empty, where
as if it has some text in it like CYUFR1234 it must be deleted from the
spreadsheet.

Thanks again,
Cathal.


---
Message posted from http://www.ExcelForum.com/

Thanks bob

--
Message posted from http://www.ExcelForum.com