Microsoft Excel - IN VBA HOW TO REMOVE DUPLICATES

Asked By vmp on 24-Nov-10 04:08 AM

how to delete duplicated in excel 2007 thru vba code.

kindly help me...

vmp
Mitesh Darji replied to vmp on 24-Nov-10 04:23 AM


This method of removing duplicates uses Excel's Advanced Filter and is about the fastest method. This example acts on data in Column "A".

Sub RemoveDupes()



	'Add extra Column, "A" becomes "B"

	Columns(1).EntireColumn.Insert

	

	'Filter out duplicates and copy unique list to "A"

	Range("B1", Range("B65536").End(xlUp)).AdvancedFilter _

		Action:=xlFilterCopy, CopyToRange:=Range("A1"), Unique:=True



	'Add extra Column, "B" becomes "A"

	Columns(2).EntireColumn.Delete



End Sub

This method of removing duplicates will work on the selected data.

Sub KillDupes()

Dim rConstRange As Range, rFormRange As Range

Dim rAllRange As Range, rCell As Range

Dim iCount As Long

Dim strAdd As String



	On Error Resume Next

	Set rAllRange = Selection

		If WorksheetFunction.CountA(rAllRange) < 2 Then

			MsgBox "You selection is not valid", vbInformation

      		On Error GoTo 0

      		Exit Sub

		End If





	Set rConstRange = rAllRange.SpecialCells(xlCellTypeConstants)

	Set rFormRange = rAllRange.SpecialCells(xlCellTypeFormulas)



    If Not rConstRange Is Nothing And Not rFormRange Is Nothing Then

		Set rAllRange = Union(rConstRange, rFormRange)

    ElseIf Not rConstRange Is Nothing Then

		Set rAllRange = rConstRange

    ElseIf Not rFormRange Is Nothing Then

		Set rAllRange = rFormRange

    Else

		MsgBox "You selection is not valid", vbInformation

		On Error GoTo 0

		Exit Sub

    End If

   

	Application.Calculation = xlCalculationManual



	For Each rCell In rAllRange

		strAdd = rCell.Address

		strAdd = rAllRange.Find(What:=rCell, After:=rCell, LookIn:=xlValues, _

			LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _

			MatchCase:=False).Address

          

		If strAdd <> rCell.Address Then

			rCell.Clear

		End If

	Next rCell



  

	Application.Calculation = xlCalculationAutomatic

	On Error GoTo 0



End Sub

I have got this code from this link:

http://www.ozgrid.com/VBA/RemoveDuplicates.htm
Mitesh Darji replied to vmp on 24-Nov-10 04:24 AM

Select the row or column that has duplicates that you would like to remove.

Go to Data -> Filter -> Advanced Filter.

Make sure the range is correct and check the option “unique records only“.

Mitesh Darji replied to vmp on 24-Nov-10 04:30 AM
How to Highlight or Remove Duplicates in Excel 2007?

I have found this article from below link:

http://www.lytebyte.com/2008/06/10/highlight-remove-delete-duplicates-excel-2007/

In the previous version of Excel, deleting duplicate cells or highlighting was a feature known only to a pro but with Excel 2007 from MS Office  deleting duplicate cells or even highlighting is an easy task

Image1:


Let us see how to delete duplicates or highlight  them one by one.

How to Remove or delete duplicate cells in Excel 2007?

Select the columns or rows where you want to find the duplicate cells.

Go to the Data tab and under data tools, click Remove duplicates. This would remove duplicate cells and their corresponding rows.

Image2



But most of the time, it deletes all the corresponding data in the rows and you may want to review the duplicate cells before deleting it. In this case highlighting the duplicate cells helps.

So how to highlight the duplicate cells in Excel 2007?

Again, select the cells that you want to highlight. Under the Home tab, under Styles, Click Conditional Formatting. Go to Highlight Cell Rules and Click Duplicate Values.

Image3


Here you can either highlight the duplicate cells or the unique cells. Choose your selection and format styles. Click Ok.

Image4


Now depending on your choices all the duplicate cells will be highlighted, you may choose to review and then delete duplicate cells based on the tips above.
Reena Jain replied to vmp on 24-Nov-10 05:46 AM
hi,

check this link for help
http://vbadud.blogspot.com/2008/07/remove-duplicate-rows-from-sheet-using.html

hope this will help you