Top VBA Codes for Excel and MS Word

🟩 Excel VBA Codes

1. Auto-Fit All Columns

Sub AutoFitAllColumns()
    Cells.EntireColumn.AutoFit
End Sub

2. Highlight Duplicate Values

Sub HighlightDuplicates()
    Dim Rng As Range
    Set Rng = Selection
    Rng.FormatConditions.AddUniqueValues
    Rng.FormatConditions(Rng.FormatConditions.Count).SetFirstPriority
    Rng.FormatConditions(1).DupeUnique = xlDuplicate
    Rng.FormatConditions(1).Interior.Color = RGB(255, 199, 206)
End Sub

3. Delete Blank Rows

Sub DeleteBlankRows()
    Dim i As Long
    For i = ActiveSheet.UsedRange.Rows.Count To 1 Step -1
        If Application.WorksheetFunction.CountA(Rows(i)) = 0 Then
            Rows(i).Delete
        End If
    Next i
End Sub

4. Protect Sheet with Password

Sub ProtectSheet()
    ActiveSheet.Protect Password:="1234"
End Sub

5. Send Email from Excel (via Outlook)

Sub SendEmail()
    Dim OutApp As Object, OutMail As Object
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    With OutMail
        .To = "someone@example.com"
        .Subject = "Test Mail from Excel"
        .Body = "Hello, this is an automated email."
        .Send
    End With
End Sub

🟦 MS Word VBA Codes

1. Toggle Text Case

Sub ToggleTextCase()
    Dim txt As String
    Dim newText As String

    txt = Selection.Range.Text

    If txt = UCase(txt) Then
        newText = StrConv(txt, vbProperCase)
    ElseIf txt = StrConv(txt, vbProperCase) Then
        newText = LCase(txt)
    Else
        newText = UCase(txt)
    End If

    Selection.Range.Text = newText
End Sub

2. Insert Current Date and Time

Sub InsertDateTime()
    Selection.TypeText Text:=Format(Now, "dd-mm-yyyy hh:mm AM/PM")
End Sub

3. Remove All Hyperlinks

Sub RemoveHyperlinks()
    Dim h As Hyperlink
    For Each h In ActiveDocument.Hyperlinks
        h.Delete
    Next h
End Sub

4. Find and Replace Text

Sub FindAndReplace()
    With Selection.Find
        .Text = "OldText"
        .Replacement.Text = "NewText"
        .Execute Replace:=wdReplaceAll
    End With
End Sub

5. Word Count of Document

Sub CountWords()
    MsgBox "Total Words: " & ActiveDocument.Words.Count
End Sub