Highlight Duplicates in Excel

Highlight Duplicates in Excel

Open Excel in VBA mode and then copy and Past  following code under the WorkSheet.Then Run the code then you can get the result with in one second.This will save your time when you work with large amount of data.

Coding

Option Explicit
----------------------------------------------------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
 
    If Target.Row = 1 Then Exit Sub             ' IF ITS A HEADER, DO NOTHING.
   
    On Error GoTo ErrHandler
    Application.ScreenUpdating = False
   
    Dim myDataRng As Range
    Dim cell As Range
   
    ' WE WILL SET THE RANGE (SECOND COLUMN).
    Set myDataRng = Range("B1:B" & Cells(Rows.Count, "B").End(xlUp).Row)
   
    For Each cell In myDataRng
        cell.Offset(0, 0).Font.Color = vbBlack          ' DEFAULT COLOR.
   
        ' LOCATE DUPLICATE VALUE(S) IN THE SPECIFIED RANGE OF DATA.
        If Application.Evaluate("COUNTIF(" & myDataRng.Address & "," & cell.Address & ")") > 1 Then
            cell.Offset(0, 0).Font.Color = vbRed        ' CHANGE FORE COLOR TO RED.
        End If
    Next cell
   
    Set myDataRng = Nothing
ErrHandler:
    Application.EnableEvents = True
    Application.ScreenUpdating = True

End Sub


Screen Shot 01

Screen Shot 02

Download Sample File Here

VLOOK UP Excel

VLOOK UP in Excel

Practical Application of VLOOK UP function in Microsoft Excel.

Download Sample File

Working with Forms in MS Excel

How to create Data Input Form in Excel

01.First Design Basic Table in Excel as follows

02.Then Move to VBA mode under developer tab,And Design User form as shown in following Screen Shot

03.Once you design user form as above then you have to place following codings.


Private Sub cmdEnter_Click()

Dim emptyRow As Long

'Make Sheet1 active
Sheet1.Activate

'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1

'Transfer information
Cells(emptyRow, 1).Value = txtName.Value
Cells(emptyRow, 2).Value = txtAddress.Value
Cells(emptyRow, 3).Value = txtAge.Value
Cells(emptyRow, 5).Value = cmbEducation.Value



If OptionButton1.Value = True Then
    Cells(emptyRow, 4).Value = "Male"

Else
    Cells(emptyRow, 4).Value = "Female"
End If

End Sub
Private Sub UserForm_Initialize()

'Empty Name Text Box
txtName.Value = ""

'Empty Address Text Box
txtAddress.Value = ""

'Empty Education Combo
cmbEducation.Clear

'Fill Education Combo

With cmbEducation
    .AddItem "OL"
    .AddItem "AL"
    .AddItem "Degree"
End With

'Set Focus on Name Text Box
txtName.SetFocus
End Sub

04.Once you completed this step you can run the form and then enter sample data.See what happens then

You Can Download Sample File here

Delete Duplicate Data in Excel

Delete Duplicate Data in Excel Using VBA Code

Steps

01.Place Command Button on Excel Sheet as shown in Figure 01

Figure 01

02.Goto VBA Mode as shown in Figure 02

 Figure 02

03.Then type following coding under the Command Button Click Event 

Private Sub CommandButton1_Click()

Dim toAdd As Boolean, uniqueNumbers As Integer, i As Integer, j As Integer

Cells(1, 2).Value = Cells(1, 1).Value

uniqueNumbers = 1

toAdd = True

For i = 2 To 1000

    For j = 1 To uniqueNumbers

        If Cells(i, 1).Value = Cells(j, 2).Value Then

            toAdd = False

        End If

    Next j

        

    If toAdd = True Then

        Cells(uniqueNumbers + 1, 2).Value = Cells(i, 1).Value

        uniqueNumbers = uniqueNumbers + 1

    End If

    

    toAdd = True

 

Next i

End Sub

Screen Shot is as follows

 04.Finally Click Excel Icon in VBA mode then enter sample duplicate items in relevant sheet and see what happens.

See Final Screen Shot