vba courses london - Duplicates

The UK's most regular instructor-led training courses.
Training information: vba courses london · Excel london

Resolved · Low Priority · Version Standard

Duplicates

by - David [guest] (2006 Nov 24 Fri, 12:42) Reply

How can you remove duplicates from a excel sheet and before sorting data into a VBA project?

RE: Duplicates

by - trainer Garry gold contributer [188 posts] (2006 Nov 27 Mon, 08:34) Reply

try this code

Sub RemoveDuplicates()

Cells.Sort Key1:=Range("A1")
totalrows = ActiveSheet.UsedRange.Rows.Count
Count = 1
For Row = totalrows To 2 Step -1
If Cells(Row, 1).Value = Cells(Row - 1, 1).Value Then
Rows(Row).Delete
Count = Count + 1
End If
Next Row

End Sub

If you’re not already familiar to writing code, the line “Count = Count + 1” will look odd to you, but don’t think of it as an algebraic equation. This is just how we add one to the current value of Count

· Introduction to Visual Basic for Applications
· Should You Send Managers To VBA Training?
· What Is Microsoft Excel VBA and How Can It Help You Work Smarter?
· How To Find A Good VB For Access Trainer
· Visual Basic Returns To The Macintosh

Vba tip:

Use VbNullString instead of

When needing to default a String variable back to it's default of "" use vbNullString as in the following example:

Sub ClearText()

Dim strEmpName As String

strEmpName = "John Smith"
MsgBox strEmpName

strEmpName = vbNullString
MsgBox strEmpName

End Sub

View all VBA hints and tips