Insert Blank Row On Change

What it does

You have a data range sorted on a key column (region, customer, date, category) and you want a blank row between each group so the file is easier to read or print. The macro asks which column to watch, then walks the selection and inserts a blank row above every cell where the value differs from the row above. Excel's Subtotal feature does this with totals, but it adds a SUM formula whether you want one or not. This macro just inserts the visual separator and leaves the numbers alone.

Real-world example

A regional sales manager at a B2B distributor in Pune is preparing the Friday afternoon territory report. The sheet has 240 rows: customer name, territory, last order date, monthly value, status. The CEO wants a one-page-per-territory printout with totals he calculates in his head, not a formula-laden subtotal stack.

Her usual method: sort by territory, scroll through, and manually insert a blank row at every territory change. About 4 minutes per report and one or two off-by-one mistakes per pass. The fix-up is annoying because adding a row in the middle shifts rows below it, and she sometimes loses her place.

She selects the data range, runs the macro, and types the column number for the territory column when prompted (in this sheet, column 2). Eight blank rows appear, one between each pair of territory groups. She prints, the CEO is happy, total time about 15 seconds.

On a 240-row sheet with 8 territory groups, the macro inserts every separator in under one second. The bigger win is that the inserts always land in the right place because the macro iterates bottom-up, never losing track of where it is. No subtotal formulas, no manual scrolling, no off-by-one. Free, offline, no signup. Your data never leaves your machine.

How to use

First time? See How to run our tools for the 30-second setup.

1. Sort your data on the column you want to group by. The macro inserts a blank row whenever the watched column's value changes, so unsorted data will produce a blank row at every change instead of between groups.
2. Select your data range. If you have a header row, exclude it from the selection. Otherwise the macro will insert a blank row between the header and row 1.
3. Press Alt + F8 (Windows) or Option + F8 (Mac), pick InsertBlankRowOnChange, click Run.
4. Enter the column number (1 = first column of your selection) to watch for value changes.
5. The macro reports how many blank rows it inserted.

FAQ

What if my data isn't sorted on the watch column?

The macro still runs, but it inserts a blank row at every value change, not just between groups. If your sales data has rows in the order East, West, East, West, you'll get a blank row between each row instead of between two groups. Sort first, then run.

Can I watch a column outside my selection?

No. The watch column must be one of the columns in your selection. Enter its position relative to the start of your selection (1 for the first column, 2 for the second, and so on). To watch a column outside the data range, expand your selection to include it.

Will it shift cells in adjacent columns I didn't select?

Yes. The macro inserts a full row, which shifts every column to the right of the data, not just the columns in your selection. If the rest of the sheet has other data that should not be shifted, copy your data range to a fresh sheet first, run the macro there, then copy the result back.

Can I undo it?

Yes, Ctrl + Z (Windows) or Cmd + Z (Mac) right after running undoes every insert in one step. If you've made other edits since, the undo stack might not reach all the way back. Always save before running on a file you can't easily redo.

Does it work on filtered data?

Not as expected. If your sheet has an active filter, the macro inserts blank rows based on the values of the visible rows only, but the inserted rows go into the underlying sheet, which can land them in the wrong spot relative to hidden rows. Clear filters before running.