VBA Quick Reference

Common snippets and shortcuts for Excel macros. Bookmark this page.

Before You Run Any Macro

  1. Save your workbook first (File → Save As → .xlsm format)
  2. Enable the Developer tab: File → Options → Customize Ribbon → check Developer
  3. Open the VBA editor: Alt+F11
  4. Insert → Module → paste the macro code
  5. Run with F5 or close editor and use Developer → Macros → Run
  6. If blocked: File → Options → Trust Center → Trust Center Settings → Macro Settings → Enable all macros (disable after testing)

Loop through all sheets

Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
    ' Do something with ws
    Debug.Print ws.Name
Next ws

Find last row in a column

Dim lastRow As Long
lastRow = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
' Column 2 = B. Change to your column.

Find last column in a row

Dim lastCol As Long
lastCol = ActiveSheet.Cells(2, Columns.Count).End(xlToLeft).Column
' Row 2. Change to your header row.

Set a range to a variable

Dim rng As Range
Set rng = ActiveSheet.Range("A1:C100")
rng.Interior.Color = vbYellow

Copy/paste values only

Range("A1:A10").Copy
Range("B1").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Show message box to user

MsgBox "Cross-foot check passed!", vbInformation, "Done"
' vbInformation = info icon
' vbExclamation = warning icon
' vbCritical = error icon

Check if cell is empty

If IsEmpty(Range("A1").Value) Then
    MsgBox "Cell A1 is empty"
End If

Format a number as currency

Range("B2:B100").NumberFormat = "$#,##0.00"
' Or for negatives in parentheses:
' .NumberFormat = "$#,##0.00_);($#,##0.00)"

Turn off screen updating (speed boost)

Application.ScreenUpdating = False
' ... your macro code ...
Application.ScreenUpdating = True
' Always turn it back on!

Count rows with data

Dim count As Long
count = WorksheetFunction.CountA(Range("A:A"))
' CountA counts non-empty cells
' Count counts numbers only

Essential Keyboard Shortcuts

Alt+F11 Open VBA editor
F5 Run macro (in editor)
Alt+F8 Macro dialog (in Excel)
Ctrl+Break Stop a running macro
F8 Step through code line by line
Ctrl+G Immediate window (debug)
Ctrl+Space Auto-complete (in editor)
F2 Object browser