Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleteing Duplicate Rows???
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleteing Duplicate Rows???
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleteing Duplicate Rows???
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleteing Duplicate Rows???
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleteing Duplicate Rows???
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/ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleteing Duplicate Rows???
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/ |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleteing Duplicate Rows???
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/ |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleteing Duplicate Rows???
Thanks a million for the help guys, this has worked for me
-- Message posted from http://www.ExcelForum.com |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleteing Duplicate Rows???
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleteing empty rows | Excel Discussion (Misc queries) | |||
Deleteing some Rows | Excel Discussion (Misc queries) | |||
Deleteing Rows | Excel Programming | |||
Deleteing Blank Rows | Excel Programming | |||
Deleteing Blank Rows | Excel Programming |