Document Editing Problems

How to Copy and Paste Noncontiguous Excel Rows Without Blanks

Olivia Miller Oct 15, 2026 871 views

Question details

The user wants to select multiple, non-adjacent rows in Excel and paste their values elsewhere without transferring any blank rows in between. They also seek a simpler, click-only selection method for non-technical users.

How to Copy and Paste Noncontiguous Excel Rows Without Blanks

Product: Microsoft Excel
Device & OS: not provided
Scenario: Transferring specific rows of data from one sheet to another without copying empty rows, while attempting to create a foolproof process for beginners who struggle with keyboard modifiers.
Observed behavior: Selecting multiple non-adjacent rows requires holding the Ctrl key, which can be difficult for some users. Standard pasting methods may accidentally include empty rows if the selection is not done precisely.

Before you start

Verify that your destination worksheet has enough empty space for the incoming rows, as pasting values over existing data will overwrite it without warning.

Solution 1Recommended

Use Keyboard Shortcuts to Paste Values Without Blanks

Utilize Excel's built-in Paste Special shortcut to quickly paste non-adjacent selections as continuous rows of values.

This is the standard and most efficient method for copying noncontiguous cells in Excel. By default, Excel requires you to hold the Ctrl key to select separate rows. When you paste these selections, Excel automatically stacks them together, eliminating the blank rows between your original selections.

Select the required rows

Hold down the Ctrl key on your keyboard and click on the specific row numbers or cell ranges you want to copy.

Copy the selection

Press Ctrl + C to copy the selected cells. A dashed line will appear around your noncontiguous selections.

Choose the destination

Navigate to the sheet or cell where you want to place the data and click the top-left cell of the destination range.

Paste as values

Press Ctrl + Alt + V to open the Paste Special dialog box, press the letter V to select Values, and hit Enter.

Use Keyboard Shortcuts to Paste Values Without Blanks

Continuous Pasting: Using this method guarantees that Excel pastes the separated data into contiguous rows at the destination without any blank gaps.

Solution 2

Create a Helper Column and Macro for Non-Technical Users

Set up a dropdown selection column paired with a VBA button to allow users to select and copy rows without needing to hold the Ctrl key.

Easily Manage and Edit Spreadsheet Data with WPS Office

WPS Spreadsheets provides a highly compatible and intuitive interface for handling everyday data tasks. You can seamlessly copy noncontiguous rows, use Paste Special to remove blanks, and utilize advanced data validation to simplify tasks for your team.

1. Open your workbook: Launch WPS Spreadsheets and open the document containing your data.
2. Select non-adjacent rows: Hold down the Ctrl key and click the specific rows or cells you need to copy.
3. Copy the data: Press Ctrl + C or right-click and select 'Copy'.
4. Paste values: Right-click the destination cell, hover over 'Paste Special', select 'Paste as Values', and your data will appear without blank rows.

Fully compatible with Microsoft Excel formats (.xlsx, .xls, .xlsm)Supports all standard keyboard shortcuts for efficient data editingLightweight application that runs smoothly on older devicesIncludes intuitive features for Data Validation and Paste Special

microsoft office alternative - wps office

Frequently Asked Questions

Can I select multiple non-adjacent rows in Excel without holding the Ctrl key?

No, Excel's native interface requires you to hold down the Ctrl key while clicking to select multiple noncontiguous cells or rows. If this is too difficult, you can set up a helper column with checkboxes or 'Yes/No' dropdowns to mark rows instead of highlighting them.

Why do blank rows appear when I copy and paste in Excel?

Blank rows appear if you select a single continuous range that happens to include empty rows. To prevent this, you must hold the Ctrl key and individually click only the rows containing data, or apply a Filter to hide the blank rows before copying the visible cells.

What is the fastest shortcut for Paste Special Values?

After copying your selection, click your destination cell and press Ctrl + Alt + V to open the Paste Special dialog. Then, press the letter V on your keyboard for 'Values' and hit Enter to confirm.

Document & File Issues