VBA snippets you'll reuse constantly

A reference list, not a tutorial — skim for the task you need, copy the snippet, adjust the sheet and range names for your workbook.

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