Excel like a finance pro.

The functions, VBA, and shortcuts a finance operator actually uses — curated, with CFO examples. Prefer them pre-built? Grab a ready-made model.

← All VBA snippets

Filter, then act on just the visible rows

AutoFilter to the rows you want, copy or process only what's visible, then clear the filter.

1What it does

Applies an AutoFilter to narrow a table to matching rows (here, Region = 'West'), then uses SpecialCells(xlCellTypeVisible) to grab ONLY the filtered rows — header included — and copy them elsewhere. Filtering in one operation and acting on the visible block is far faster than looping every row and testing a condition, and it's how you split, extract, or bulk-edit a subset.

2The code

Sub FilterAndCopy()
    Dim ws As Worksheet, lastRow As Long
    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' Clear any existing filter, then filter column 2 (Region) to "West".
    ws.AutoFilterMode = False
    ws.Range("A1:C" & lastRow).AutoFilter Field:=2, Criteria1:="West"

    ' Copy just the VISIBLE (filtered) rows to a "West" sheet — the header comes
    ' along. SpecialCells(xlCellTypeVisible) picks up only what's on screen.
    ws.Range("A1:C" & lastRow).SpecialCells(xlCellTypeVisible).Copy _
        Worksheets("West").Range("A1")

    ws.AutoFilterMode = False   ' clear the filter when done
End Sub

Paste into the Visual Basic Editor (Alt + F11 → Insert → Module), then run and save as macro-enabled (.xlsm).

New to macros? Set up in 5 minutes▾

1
Don't see the Developer tab in the ribbon?
You don't strictly need it — Alt + F11 opens the editor directly — but it makes running macros easier.
- Windows: File → Options → Customize Ribbon → tick Developer in the right-hand list → OK.
- Mac:Excel → Preferences → Ribbon & Toolbar → tick Developer → Save.
2
Paste in the code
Press Alt + F11 to open the Visual Basic editor, then Insert → Moduleand paste the snippet's code into the blank window. Close it with Alt + Q.
3
Run it
Press Alt + F8, pick the macro's name, and click Run — that's it. (Pasted a custom function instead? Just type it into a cell like any built-in: =GrossMargin(B2, B3).)
4
Keep the macro — save as .xlsm
File → Save As → Excel Macro-Enabled Workbook (.xlsm). A plain .xlsx silently drops the code when you save.
5
Macros blocked?
Click Enable Content on the yellow bar. If you downloaded the file, you may first need to right-click it → Properties → tick Unblock → OK, then reopen.

Heads up: macros can't be undone with Ctrl + Z — save a copy before running one that changes your workbook.

3When you use it

Pull every row for one region / month / status onto its own sheet.
Bulk-delete or bulk-edit a subset without looping row by row.
Extract matches to send or archive.

4See it in action

A simulation — press Run to perform what the macro does to a sample workbook.

Month	Region	Amount
Jan	East	$1,200
Feb	West	$800
Mar	West	$1,750
Apr	East	$300

5Pitfalls

SpecialCells errors when the filter matched nothing.

Fix: Wrap the copy in On Error Resume Next, or check the visible count with Subtotal(3, ...) > 1 before acting.

A filter left on from a previous run skews the result.

Fix: Reset with ws.AutoFilterMode = False before applying the new filter, and again when done.

6No-code alternatives

Advanced Filter — Copy matches to another location via a criteria range — no code, good for complex criteria.
Loop + test — Simpler to read on small data, but slow and clumsy for extract / delete at scale.

Rather not write macros? Wauvel's free tools generate branded, formula-driven Excel for you — no VBA required.

Learn the moves here — or let Wauvel run them on your numbers.

Get my free report →