bharat baradia


hi,

i want to delete the duplicate rows in the excel sheet and copy the same deleted records into another excel sheet by using Macro's . This should be possible even when i am selecting two columns for checking duplicates and even if one of the column is repeated ( is duplicate) it should be considered as a duplicate.




Re: Copying and Deleting the duplicate records in an excel sheet and Copying the same Deleted records into another sheet

js06


Do you want to do this with Visual Basic Express or with VBA in excel

If you want to use macros then you would probably get better help in the VBA forum







Re: Copying and Deleting the duplicate records in an excel sheet and Copying the same Deleted records into another sheet

Feng Chen - MSFT

Hi bharat baradia,

Move the thread from Visual Basic Express Edition in order to get better answers.
Thanks for your understanding!







Re: Copying and Deleting the duplicate records in an excel sheet and Copying the same Deleted records into another sheet

ADG

Hi

Can you post some example data so that what you are asking is clearer please. Also will it always be the same columns Will the data be continuous or will there be blank lines

Regards

ADG





Re: Copying and Deleting the duplicate records in an excel sheet and Copying the same Deleted records into another sheet

magicalclick

Instead of doing this in one macro, I suggest you to do this in 2 stages. Key Generation and then Scan procedure. This keeps functions more resueable in the futrure.

1) you make an extra column by combining the partial key columns. It is better to insert an special separater to make sure you don't end up with the same key like this, he + llo = hello and hell + o = hello.

2) Compare the primary key and start copying the row and delete it.

I am only going to provide you with few helper functions. Everything else should be easy for you. The LastRow function will gives you the true last row in your desired range so you can loop each row from top to last row. The Find_inOneColumn functions will tell you if duplicate value is found in the same column or not and has an optional Return parameter you tell you where the duplicate is found. This is not needed if you sorted the rows with your new primary key. This is used for keeping the original records' order. There are other ways to keep the original ordering, but, nah.

Hope this helps.

'========================= Code ================================================================

Function LastRow(Optional rTest As Range) As Long
If IsNull(rTest) Then Set rTest = ActiveSheet.UsedRange
If rTest Is Nothing Then Set rTest = ActiveSheet.UsedRange
Dim lTest As Long
Dim iCol As Range
For Each iCol In rTest.Columns
With rTest.Parent.Cells(Rows.Count, iCol.Column)
If .Value <> "" Then LastRow = .Row: Exit Function
lTest = IIf(.End(xlUp).Row > lTest, .End(xlUp).Row, lTest)
End With
Next
LastRow = lTest
End Function

Function Find_inOneColumn(vWhat As String, Optional vAfter As Range, _
Optional vLookIn As emFindParameters = LookIn_Formulas, _
Optional vLookAt As emFindParameters = LookAt_Part, _
Optional vSearchDirection As emFindParameters = SearchDirection_Next, _
Optional vMatchCase As Boolean = False, Optional ByRef vReturn As Range) As Boolean
Dim CurrentCol As Long
If IsNull(vAfter) Then Set vAfter = Range("A1")
CurrentCol = vAfter.Column
Find_inOneColumn = Find(vWhat, vAfter, vLookIn, vLookAt, SearchOrder_ByColumns, vSearchDirection, vMatchCase, vReturn)
If Find_inOneColumn = True Then If CurrentCol <> vReturn.Column Then Find_inOneColumn = False
End Function

Function Find(vWhat As String, Optional vAfter As Range, _
Optional vLookIn As emFindParameters = LookIn_Formulas, _
Optional vLookAt As emFindParameters = LookAt_Part, _
Optional vSearchOrder As emFindParameters = SearchOrder_ByRows, _
Optional vSearchDirection As emFindParameters = SearchDirection_Next, _
Optional vMatchCase As Boolean = False, Optional ByRef vReturn As Range) As Boolean
If IsNull(vAfter) Then Set vAfter = Range("A1")
On Error Resume Next
Set vReturn = Nothing
Set vReturn = Cells.Find(What:=vWhat, After:=vAfter, _
LookIn:=vLookIn, LookAt:=vLookAt, _
SearchOrder:=vSearchOrder, SearchDirection:=vSearchDirection, _
MatchCase:=vMatchCase)
'SearchFormat:=vSearchForma ' took out for Excel2000
If vReturn Is Nothing Then
Find = False
Else
Find = True
End If
End Function