Document Editing Problems

How to Copy Noncontiguous Rows with Freeze Panes in Excel

Tauseeq Magsi Oct 16, 2026 869 views

Question details

The user wants to copy only the visible, noncontiguous rows on their screen (e.g., rows 1-8 and 20-30) while using the Freeze Panes feature, without copying the scrolled-past rows (9-19) in between.

How to Copy Noncontiguous Rows While Using Freeze Panes

Product: Excel
Device & OS: not provided
Scenario: Scrolling through a large dataset with Freeze Panes active and needing to copy the top frozen rows alongside the currently visible bottom rows as a single dataset.
Observed behavior: Excel does not provide a built-in, single-click option to copy separate visible areas created dynamically by scrolling under Freeze Panes. Dragging selects the hidden rows in between.

Before you start

Before attempting to copy noncontiguous rows, ensure your data does not contain merged cells across the rows you intend to copy, as this can prevent spreadsheet programs from copying multiple selections.

Solution 1Recommended

Select and Copy Noncontiguous Rows Manually

Since a simple drag-and-drop selection will include the hidden rows under the frozen pane, use the keyboard modifier key to select specific rows manually.

While Freeze Panes visually hides the middle rows as you scroll, Excel still considers them part of the active selection if you click and drag from the top row to the bottom row. Using the Ctrl (or Command) key allows you to pick exactly which blocks of rows to copy.

Select the first range

Click and drag on the row numbers on the left side of your screen to select the first set of frozen rows (e.g., rows 1 to 8).

Hold the selection modifier key

Press and hold down the Ctrl key on Windows, or the Command key on a Mac.

Select the second range

While holding the key, click and drag on the row numbers for the second set of visible rows in your scrolled area (e.g., rows 20 to 30).

Copy and paste

Press Ctrl + C (or Cmd + C) to copy the selected rows. Navigate to your destination sheet and press Ctrl + V to paste them. They will paste seamlessly together.

Select and Copy Noncontiguous Rows Manually

Selection Tip: Always click directly on the row numbers (the gray headers on the far left) rather than selecting the cells inside the rows. This ensures the entire row is selected evenly, preventing multiple selection errors.

Solution 2

Hide In-Between Rows and Copy Visible Cells

If manually holding Ctrl is tedious or prone to error in large datasets, you can temporarily hide the middle rows and use the 'Visible cells only' command.

Solution 3

Use VBA to Automate Copying Split Ranges

For advanced users who frequently need to extract specific split ranges from large reports, a VBA macro can bypass manual selection entirely.

Work Smarter with WPS Office

Copy and Manage Noncontiguous Data Easily in WPS Spreadsheet

WPS Spreadsheet provides a highly compatible and intuitive interface for managing large datasets. You can easily select noncontiguous rows, hide unwanted data, and copy visible cells without running into complex spreadsheet errors.

1. Open your dataset: Launch WPS Spreadsheet and open your workbook.
2. Enable Freeze Panes: Navigate to the View tab, select the row below your header, and click Freeze Panes to lock the top rows.
3. Select the top rows: Scroll down to your target data. Click the row numbers on the left to select your top frozen rows.
4. Select the bottom rows: Hold down the Ctrl key and click the row numbers for the currently visible bottom rows you want to include.
5. Copy and paste: Press Ctrl + C to copy your combined selection, open a new worksheet, and press Ctrl + V to paste the contiguous data seamlessly.

Fully compatible with Microsoft Excel (.xlsx, .xls) files and formulas.Intuitive selection tools and 'Go To Special' feature for managing visible cells.Lightweight application with fast processing speeds for large datasets.Free to download and use with a highly familiar tabbed interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel say 'That command cannot be used on multiple selections'?

This error usually occurs when you try to copy noncontiguous cells or rows that do not form a perfectly aligned rectangular grid, or if you accidentally select overlapping ranges. Selecting entire rows evenly by clicking their row numbers usually prevents this issue.

Can I copy only the frozen panes in Excel without the rest of the data?

Yes, you can easily copy just the frozen panes. Since Freeze Panes is simply a display feature that locks rows in place, you can click and drag across the row numbers above the freeze line and press Ctrl+C to copy them like any normal data.

Is there a keyboard shortcut to select only visible cells?

Yes. After highlighting a large range that includes hidden or filtered rows, press Alt + ; (semicolon) on Windows. This instantly restricts your selection to only the visible cells, allowing you to copy them without including the hidden data.

Document & File Issues