For IndividualsFor Analysts & FinanceFor Students & LearnersFor Teams & Automation
MENU
=ARTICLES

VBA snippets you'll reuse constantly

VBA.B3 Cells(Rows.Count, 1).End(xlUp).Row
VBA snippets you'll reuse constantly — Excel example preview

Finding the true last populated row dynamically with End(xlUp) instead of hardcoding row numbers in VBA macros.

=YOUTUBE

Watch alongside this article

Public YouTube resources matched to this article's topic, functions, and practice goal.

video / Advanced

The Ultimate Excel Advanced Filters In VBA [Full Training Course]

Excel For Freelancers

Open on YouTube (opens in a new tab)
Excel For Freelancers

Loading preview…

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 Explicit at 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.

=PRACTICE

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

  1. Rebuild the example once exactly as described, then repeat it with different labels, dates, or amounts.
  2. Write a short note beside the result explaining what each step is doing and why it matters.
  3. Change one input value and confirm the output updates in the way you expected.
  4. 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.