VBA snippets you'll reuse constantly

Finding the true last populated row dynamically with End(xlUp) instead of hardcoding row numbers in VBA macros.
Watch alongside this article
Public YouTube resources matched to this article's topic, functions, and practice goal.


The Ultimate Excel Advanced Filters In VBA [Full Training Course]
Excel For Freelancers
Open on YouTube (opens in a new tab)
A reference list, not a tutorial — skim for the task you need, copy the snippet, adjust the sheet and range names for your workbook.
Two things to do before pasting any of it:
- Put
Option Explicitat the top of every module. Tools → Options → Editor → “Require Variable Declaration” makes it automatic in new modules. Without it a typo in a variable name silently creates a new empty variable, and you will lose an hour to it eventually. - Qualify your sheets.
Range("A1")means “A1 on whatever sheet happens to be active”, which is fine until a macro runs with the wrong tab in front.ThisWorkbook.Worksheets("Data").Range("A1")means what you meant. The snippets below are written short for readability; qualify them in real code.
Find the last used row
The most common way to find where your data ends, working up from the bottom of the sheet so it ignores blank cells below the data:
Dim lastRow As Long
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
Find the last used column
Same idea, working leftward from the far right edge of the sheet:
Dim lastCol As Long
lastCol = Cells(1, Columns.Count).End(xlToLeft).Column
Turn off screen updating for speed
Wrap this around anything that touches a lot of cells — it stops Excel from redrawing after every single action, which is the single biggest speed win available:
Application.ScreenUpdating = False
' ... your code here ...
Application.ScreenUpdating = True
Loop through every file in a folder
Dir returns matching file names one at a time — call it with a pattern
first, then with no arguments to get the next match:
Dim folderPath As String, fileName As String
folderPath = "C:\Reports\"
fileName = Dir(folderPath & "*.xlsx")
Do While fileName <> ""
Debug.Print fileName
fileName = Dir
Loop
Basic error handling with On Error
Exit Sub before the label keeps the handler from running when nothing
went wrong:
Sub SafeDivide()
On Error GoTo ErrHandler
Dim result As Double
result = 10 / Range("A1").Value
MsgBox result
Exit Sub
ErrHandler:
MsgBox "Something went wrong: " & Err.Description
End Sub
A simple InputBox / MsgBox pattern
InputBox returns an empty string if the user clicks Cancel, so check
for that before using the result:
Dim userName As String
userName = InputBox("What's your name?", "Greeting")
If userName <> "" Then
MsgBox "Hello, " & userName & "!"
End If
Copy a range to another sheet
The destination only needs its top-left cell — Excel fills in the rest of the shape automatically:
Sheets("Data").Range("A1:D20").Copy Destination:=Sheets("Summary").Range("A1")
Delete blank rows
Loop backwards from the last row to the first — deleting forward shifts rows up and skips the next one every time you delete:
Dim i As Long, lastRow As Long
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = lastRow To 1 Step -1
If Application.WorksheetFunction.CountA(Rows(i)) = 0 Then
Rows(i).Delete
End If
Next i
Check whether a sheet exists before referencing it
Useful before you try to write to a sheet that a previous step might not have created yet:
Function SheetExists(sheetName As String) As Boolean
Dim ws As Worksheet
On Error Resume Next
Set ws = ThisWorkbook.Worksheets(sheetName)
On Error GoTo 0
SheetExists = Not ws Is Nothing
End Function
Suppress confirmation prompts for one operation
Some actions — like saving over an existing file, or deleting a sheet — pop up a confirmation dialog. Turn alerts off just for that line, then straight back on:
Application.DisplayAlerts = False
ThisWorkbook.Save
Application.DisplayAlerts = True
Read a range into an array, work, write it back
The single biggest performance pattern in VBA, and the one most macros are missing. Touching the worksheet is expensive; touching an array is not. A loop that reads and writes cells one at a time over 20,000 rows takes minutes — the same work in an array takes under a second.
Sub FastCalc()
Dim data As Variant
Dim i As Long
data = Sheets("Data").Range("A2:D20000").Value ' one read
For i = 1 To UBound(data, 1)
data(i, 4) = data(i, 2) * data(i, 3) ' qty * price
Next i
Sheets("Data").Range("A2:D20000").Value = data ' one write
End Sub
Note the array from .Value is 1-based and two-dimensional —
data(row, column) — which trips up anyone coming from a zero-based language.
The standard speed-up wrapper
ScreenUpdating is the famous one, but on a workbook with formulas,
Calculation usually matters more. Always restore the settings, including when
the macro errors — otherwise you leave Excel in manual calculation and someone
spends the afternoon wondering why their numbers are stale.
Sub WithSpeedUp()
Dim calcMode As XlCalculation
calcMode = Application.Calculation
On Error GoTo Cleanup
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
' ... your code here ...
Cleanup:
Application.EnableEvents = True
Application.Calculation = calcMode
Application.ScreenUpdating = True
If Err.Number <> 0 Then MsgBox "Failed: " & Err.Description
End Sub
EnableEvents = False matters whenever your macro writes to cells that have a
Worksheet_Change handler — without it the handler fires on every write, which
is slow and occasionally recursive. See
workbook and worksheet events.
Find a value without looping
Find is far faster than scanning cells yourself, and returns Nothing rather
than erroring when there is no match — so always test for it.
Dim found As Range
Set found = Sheets("Data").Columns("A").Find( _
What:="INV-1042", LookIn:=xlValues, LookAt:=xlWhole)
If Not found Is Nothing Then
MsgBox "Found on row " & found.Row
Else
MsgBox "Not found"
End If
Always pass LookAt explicitly. Find remembers the last settings used —
including ones a user set in the Find dialog — so omitting it makes the result
depend on invisible state.
Delete rows in one pass instead of looping
The backwards loop earlier works, but on large sheets it is slow because each
.Delete shifts everything below. Collecting the rows into one Range and
deleting once is dramatically faster.
Sub DeleteBlankRowsFast()
Dim i As Long, lastRow As Long
Dim victims As Range
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To lastRow
If Application.WorksheetFunction.CountA(Rows(i)) = 0 Then
If victims Is Nothing Then
Set victims = Rows(i)
Else
Set victims = Union(victims, Rows(i))
End If
End If
Next i
If Not victims Is Nothing Then victims.Delete
End Sub
Add a worksheet, replacing any existing one
Most “sheet already exists” errors come from a macro that is run twice. This deletes the old one silently and starts clean.
Function FreshSheet(sheetName As String) As Worksheet
Application.DisplayAlerts = False
On Error Resume Next
ThisWorkbook.Worksheets(sheetName).Delete
On Error GoTo 0
Application.DisplayAlerts = True
Set FreshSheet = ThisWorkbook.Worksheets.Add( _
After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
FreshSheet.Name = sheetName
End Function
Export a sheet as PDF
The parameters are worth keeping because nobody remembers them.
Sheets("Dashboard").ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=ThisWorkbook.Path & "\Dashboard " & Format(Date, "yyyy-mm-dd") & ".pdf", _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
Set the print area and page setup first, or you get whatever Excel decides — printing an Excel dashboard to PDF covers getting that right.
Timestamp what the macro did
Unattended macros fail silently. A one-line log on a hidden sheet is the cheapest possible answer to “when did this last run, and did it work?”
Sub LogRun(message As String)
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Log")
With ws.Cells(ws.Rows.Count, 1).End(xlUp).Offset(1, 0)
.Value = Now
.Offset(0, 1).Value = message
End With
End Sub
A note on undo
Nothing a macro does can be undone. Running one clears Excel’s entire undo
stack, so a mistake is permanent and Ctrl + Z will not save you.
For anything destructive, either save first (ThisWorkbook.Save), work on a
copy of the sheet, or ask before proceeding:
If MsgBox("Delete " & victims.Rows.Count & " rows?", _
vbYesNo + vbExclamation) <> vbYes Then Exit Sub
Then put real error handling around it — VBA error handling and debugging covers the patterns, and loops and conditions covers the control flow these snippets assume.
FREE EXCEL UPDATES
Get practical Excel tips in your inbox
New tutorials, tools, templates, and worked examples a few times a month. Unsubscribe anytime.
Go deeper with this skill
Turn the idea into a repeatable Excel workflow you can explain, rebuild, and review later. For this article, the goal is to practice: Copy-pasteable VBA for the things every macro needs — finding the last row, looping a folder, handling errors, and the array pattern that makes a slow macro fast.
Practice workbook setup
Create a small practice workbook with one raw-data sheet, one working sheet, and one final output sheet.
Practice workflow
- Rebuild the example once exactly as described, then repeat it with different labels, dates, or amounts.
- Write a short note beside the result explaining what each step is doing and why it matters.
- Change one input value and confirm the output updates in the way you expected.
- Save a clean copy of the workbook before experimenting further.
Quality checks
- Inputs, calculations, and final outputs are separated clearly.
- Headings describe the data without relying on memory or hidden context.
- The final result can be understood by someone who did not build the workbook.
Common mistakes
- Mixing raw data and manual adjustments in the same cells.
- Skipping a quick review after the result looks correct.
- Building a one-off fix instead of a repeatable workflow.
Next actions
- Apply the same pattern to a real workbook with 20 to 50 rows of sample data.
- Add one note that explains when you would not use this approach.
Formula focus: even if this workflow is not formula-heavy, add one check cell that confirms the final output still matches the source data.






