Cells and Rows

The Cells and Rows group on the DVD Addin tab is the toolkit for taking in and cleaning up a workbook: widening a table by many rows or columns at once, throwing out junk rows, slimming down a bloated file, merging cells for a form, and joining values or cell addresses into configuration strings.

These are the commands you reach for when a bill of quantities arrives from a subcontractor, comes out of estimating software, or has just been converted from PDF.

Commands in this group

Command	What it does
Insert Rows	Inserts N blank rows above the active cell (N = 1…1000)
Insert Columns	Inserts N blank columns left of the active cell — same window, other tab
Insert and Copy	Replicates a template block of rows under many target rows (shortcut Alt+C)
Delete blanks	Deletes rows with no data at all inside the range you point at
Delete hidden rows	Permanently removes rows that are currently hidden (filter, manual hide, collapsed group)
Delete unused formats	Clears formatting outside the real data area of the active sheet and recomputes the used range
Delete error Defined Names	Deletes broken Defined Names (#REF!, external paths) and hidden ones
Strip junk styles	Opens a closed file on disk and strips junk Cell Styles from xl/styles.xml
Merge keep data	Merges but joins all content, preserving per-character formatting (Ctrl+Shift+Z)
Merge similar	Merges runs of adjacent cells holding the same value
Merge rows by key License	Groups rows sharing column 1; numeric cells become a formula such as =12.5+8.4
Auto group License	Builds Excel outline groups from an outline-number column (1 / 1.1 / 1.1.1) or plain level numbers
AutoFit merged	Measures and applies the right row height for merged cells — something Excel's AutoFit cannot do
Select visible	Narrows the selection to the visible cells only (Go To Special ▸ Visible cells)
Copy/Paste visible License	Copies between two ranges counting only visible rows on both sides, with an optional key-lookup mode
Join Text	Joins the values of the populated cells into one string written to a target cell
Join Merged	Joins the top-left address of each merged area, producing strings such as F8+F10

The three License-badged commands need a valid DVDAddin account; during the trial period they are fully available.

Taking in a subcontractor's quantity file

1. Save a copy first — almost nothing in this group can be undone with Ctrl+Z.
2. Click Delete error Defined Names. No selection is needed; it works on the active workbook, showing Scanning name 300/1250 and then the count removed. These names are the usual reason Excel raises a duplicate-name prompt hundreds of times when you copy a sheet.
3. Click Delete blanks, pick the data range in the "Select data range to delete blank rows" prompt, and click OK. By default blank rows that are currently hidden are kept — change that under Preferences ▸ Cells and Rows ▸ "Skip hidden rows".
4. Run Delete unused formats on each sheet so Ctrl+End lands on the real last cell, then save and reopen the file — the size usually drops only after that.
5. Still heavy and slow to open? Close the file completely, click Strip junk styles, and pick it in the "Pick xlsx file" dialog (.xlsx, .xlsm, .xlam).
6. To consolidate by work code: select the data block (no header row, at least 2 columns × 2 rows, column 1 is the key) and run Merge rows by key.
7. Presentation last: Merge similar on the repeating section column, Auto group for the +/- outline buttons, and AutoFit merged right before printing.

Hold ESC while a scan or delete is running to stop early — whatever was already processed stays.

Example

Sheet TONGHOP_KL received from a subcontractor: 480 rows of which only 320 hold data, Ctrl+End jumps to BX65000, and the file weighs 42 MB.

Step	Command	Status bar
1	Delete error Defined Names	Deleted 812/1250 broken/hidden named ranges
2	Delete blanks (range A5:H484)	Deleted 160 blank row(s).
3	Delete unused formats	Cleaned formats outside the data area (last: R320C11).
4	Save, close, reopen	Ctrl+End stops at K320, file down to roughly 3 MB

Then select the "Section" column where "Foundation M1" repeats over ten rows → Merge similar → one merged cell spanning all ten.

Tips and caveats

• Delete blanks and Delete hidden rows delete entire sheet rows, not just the part inside your selection — another table sitting to the right on the same rows loses data too.
• Rows hidden by a collapsed outline group count as hidden and will be removed by Delete hidden rows; expand everything first if you are not certain.
• Strip junk styles rewrites the original file in place, makes no backup, and refuses to run while the file is open in Excel.
• Merge similar treats an empty cell as "same as the one above" and pulls it into the current run — fill the gaps before running it. Cells inside an Excel Table (ListObject) cannot be merged.
• Merge rows by key clears the selected range and rewrites the result; numeric cells become an additive formula, so use Paste Special ▸ Values afterwards if you need static numbers.
• Join Text takes the raw cell content, not the formatted display string — dates and specially formatted numbers come out in their underlying form. The result is static text and does not refresh.
• Join Text and Join Merged share one separator, set in Preferences → Cells and Rows → Delimiter for Join Text: Space / Comma / Semicolon / Newline / Custom. Set Custom = + to produce the YCNTCV+BBNTCV+KT_DD form that Batch print expects.
• Insert Columns shifts print areas, defined names and any Quick Input source rules written as addresses — re-check them afterwards.

See also

• Numbering — renumber the No. column and multilevel codes once the blank rows are gone.
• Smart Data Cleaning — clean the content next: whitespace, casing, dates, duplicate rows.
• Batch print — the consumer of the configuration strings Join Text and Join Merged produce.