20 VBA Snippets You’ll Reuse Forever (2025 Edition)

VBA (Visual Basic for Applications) may be decades old, but in 2025 it’s still one of the most effective ways to automate tasks inside Excel. 

The beauty of VBA is that you don’t need to build massive projects — often, a few simple snippets of code can save hours every week.

 

Here are 20 ready-to-use VBA snippets you’ll find yourself reusing forever — from looping through data to cleaning, formatting, and exporting.

Loops & Navigation

1. Loop Through All Worksheets

Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
   MsgBox ws.Name
Next ws

2. Loop Through All Rows in a Range

Dim cell As Range
For Each cell In Range("A1:A100")
   cell.Value = UCase(cell.Value)
Next cell

3. Find the Last Row with Data

LastRow = Cells(Rows.Count, 1).End(xlUp).Row

4. Find the Last Column with Data

LastCol = Cells(1, Columns.Count).End(xlToLeft).Column

Copy & Paste

5. Copy and Paste Values

Range("B1").Value = Range("A1").Value

6. Copy Entire Column to Another Sheet

Sheets("Sheet2").Range("A:A").Value = Sheets("Sheet1").Range("A:A").Value

7. Copy UsedRange from One Sheet to Another

Sheets("Sheet2").Range("A1").Value = Sheets("Sheet1").UsedRange.Value

Data Cleaning

8. Trim Spaces from a Range

For Each cell In Selection
   cell.Value = Trim(cell.Value)
Next cell

9. Convert Text to Proper Case

For Each cell In Selection
   cell.Value = Application.WorksheetFunction.Proper(cell.Value)
Next cell

10. Remove Duplicates from a Range

Selection.RemoveDuplicates Columns:=1, Header:=xlYes

Formatting

11. Autofit All Columns

Cells.EntireColumn.AutoFit

12. Highlight Duplicates

Selection.FormatConditions.AddUniqueValues
Selection.FormatConditions(Selection.FormatConditions.Count). _
   SetFirstPriority
Selection.FormatConditions(1).DupeUnique = xlDuplicate
Selection.FormatConditions(1).Interior.Color = vbYellow

13. Add Borders Around a Range

Range("A1:D10").Borders.LineStyle = xlContinuous

14. Color Alternate Rows (Zebra Stripes)

For i = 1 To 100
   If i Mod 2 = 0 Then Rows(i).Interior.Color = RGB(230, 230, 230)
Next i

Pivot Tables & Charts

15. Refresh All Pivot Tables

Dim pt As PivotTable, ws As Worksheet
For Each ws In Worksheets
   For Each pt In ws.PivotTables
       pt.RefreshTable
   Next pt
Next ws

16. Create a Simple Chart Automatically

Charts.Add
ActiveChart.SetSourceData Source:=Range("A1:B10")
ActiveChart.ChartType = xlColumnClustered

Files & Reports

17. Save Workbook Backup Copy

ThisWorkbook.SaveCopyAs "C:\Backups\" & ThisWorkbook.Name

18. Export Sheet as PDF

Sheets("Report").ExportAsFixedFormat _
   Type:=xlTypePDF, Filename:="C:\Reports\Report.pdf"

19. Merge Multiple Workbooks in a Folder

Dim wb As Workbook, f As String
f = Dir("C:\Data\*.xlsx")
Do While f <> ""
   Set wb = Workbooks.Open("C:\Data\" & f)
   wb.Sheets(1).UsedRange.Copy ThisWorkbook.Sheets("Master").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
   wb.Close False
   f = Dir
Loop

20. Send Report via Outlook

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

With OutMail
   .To = "manager@example.com"
   .Subject = "Monthly Report"
   .Body = "Please find the report attached."
   .Attachments.Add "C:\Reports\Report.pdf"
   .Send
End With

Final Thoughts

These 20 snippets cover 80% of what most professionals need to automate in Excel: loops, formatting, cleaning, reporting, and integration. The best part? You don’t need to memorize them — just keep them handy, and adapt them as needed.

Editor’s Note

At ExcelGoodies, our VBA Macro Programming Course takes you beyond snippets — teaching you how to write, adapt, and combine code into complete automation solutions. With hands-on finance and reporting use cases, you’ll learn how to turn these building blocks into powerful, time-saving tools.

Because real productivity isn’t about knowing one trick — it’s about knowing how to make Excel work for you.

Courtesy: Excelgoodies Power Users.
 

VBA & Python