🟩 TOP VBA CODES FOR EXCEL
✅ 1. Auto-Fit All Columns
vb
Sub AutoFitAllColumns()
Cells.EntireColumn.AutoFit
End Sub
✅ 2. Highlight Duplicate Values
vb
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
vba
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
vba
Copy
Edit
Sub ProtectSheet()
ActiveSheet.Protect Password:="1234"
End Sub
✅ 5. Send Email from Excel (via Outlook)
vba
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
🟦 TOP VBA CODES FOR MS WORD
✅ 1. Toggle Text Case (Upper → Proper → Lower)
vb
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
vba
Copy
Edit
Sub InsertDateTime()
Selection.TypeText Text:=Format(Now, "dd-mm-yyyy hh:mm AM/PM")
End Sub
✅ 3. Remove All Hyperlinks
vba
Sub RemoveHyperlinks()
Dim h As Hyperlink
For Each h In ActiveDocument.Hyperlinks
h.Delete
Next h
End Sub
✅ 4. Find and Replace Text
vba
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
Comments
Post a Comment