Note: The other languages of the website are Google-translated. Back to English
Log in Register
English English▼
X
en Englishar Arabichy Armenianzh-CN Chinese (Simplified)zh-TW Chinese (Traditional)cs Czechda Danishnl Dutchfr Frenchde Germanel Greekhu Hungarianid Indonesianga Irishit Italianja Japaneseko Koreanpl Polishpt Portuguesero Romanianru Russiansl Slovenianes Spanishsv Swedishth Thaitr Turkishuk Ukrainianvi Vietnamesecy Welsh
  • Documents
  • Excel
  • How to copy and paste only non-blank cells in Excel?

How to copy and paste only non-blank cells in Excel?

Most of us may be suffered with this problem, when we copy a list of data which contain some blank cells, and then paste them to another column, the blank cells will also be pasted as well. This is very annoying when we don’t need the blanks and want to paste non-blank cells only. In this case, how could we copy and paste only non-blank cells in Excel quickly and conveniently? Here are some quick tricks for you to solve it:

Copy and paste only non-blank cells with Go To Special command

Copy and paste only non-blank cells with Filter feature

Copy and paste only non-blank cells with Array Formula

Copy and paste only non-blank cells with VBA code

Copy and paste only non-blank cells with Kutools for Excel good idea3

arrow blue right bubble Copy and paste only non-blank cells with Go To Special command

With the Go To Special command, you can select all the data first, and then copy and paste them to another location.

1. Select your list of data that you want to use.

doc-copy-only-nonblanks1

2. Then click Home > Find & Select > Go To Special, see screenshot:

doc-copy-only-nonblanks1

3. In the Go To Special dialog box, check Constants option, see screenshot:

doc-copy-only-nonblanks1

4. Then click OK, and only the value cells have been selected in the list.

doc-copy-only-nonblanks1

5. And then copy and paste the data to a location that you want. And only the non-blank cell values have been pasted. See screenshot:

doc-copy-only-nonblanks1

Note: This method only be available for the constants, it is not applied to the formula cells.

arrow blue right bubble Copy and paste only non-blank cells with Filter feature

The Filter feature can help you filter the non-blank cells first, and then you can copy and paste them to another cells as you need.

2. Click Data > Filter, see screenshot:

doc copy only nonblanks6

3. Then click the drop down button at the right corner of the cell in the selected list, and uncheck the Blanks option under the drop down menu. See screenshot:
doc-copy-only-nonblanks1

4. And then click OK, all the non-blank cells have been filtered out as following screenshot shown:

doc-copy-only-nonblanks1

5. At last, you can copy the filtered data and paste them to any location you need.

Note: Both values and formulas are taken effect by using this way.

Select nonblank cells at once
Click for free full featured trial in
30
days!
doc kutools select non blank cells

arrow blue right bubble Copy and paste only non-blank cells with Array Formula

To copy and paste only the non-blank cells, you can also apply the following array formula.

1. Besides your data, enter the following formula to a blank cell:

=LOOKUP("zzzzz",CHOOSE({1,2},"",INDEX(A:A,SMALL(IF($A$1:$A$15<>"",ROW($A$1:$A$15)),ROWS($B$1:B1)))))

doc-copy-only-nonblanks1

Note: In the above formula, A1:A15 is the data list that you want to use.You can change it to your need.

2. Then press Shift + Ctrl + Enter keys together, and then select cell B1, and drag the fill handle over to the range that you want to contain this formula, and all the non-blank cell values have been extracted. See screenshot:

doc-copy-only-nonblanks1

3. As they are formulas, when you copy them to other place, you should copy and paste them as values.

Note: This formula only be available for the constants, not applied to the formula cells.

arrow blue right bubble Copy and paste only non-blank cells with VBA code

If you are interested in VBA code, you can execute the following code to finish this task.

1. Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in the Module Window.

VBA code: Copy and paste only non-blank cells in Excel

Sub PasteNotBlanks()
'Update 20140325
Dim rng As Range
Dim InputRng As Range, OutRng As Range
xTitleId = "KutoolsforExcel"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Range :", xTitleId, InputRng.Address, Type:=8)
If InputRng.Columns.Count > 1 Then
    MsgBox "Please select one column."
    Exit Sub
End If
Set OutRng = Application.InputBox("Out put to (single cell):", xTitleId, Type:=8)
InputRng.SpecialCells(xlCellTypeConstants).Copy Destination:=OutRng.Range("A1")
End Sub

3. Then press F5 key to run this code, a prompt box will appear to remind you selecting the data range that you want to use. See screenshot:

doc-copy-only-nonblanks1

4. And then click OK, another prompt box will pop out to let you choose a cell to put the data.

doc-copy-only-nonblanks1

5. Click OK to finish this code, and only the non-blank cell values have been pasted into your specified position.

Note: This code is only available to the constants.

arrow blue right bubble Copy and paste only non-blank cells with Kutools for Excel

Is there any much easier way than above? Of course, Kutools for Excel's Select Nonblank Cells utility can help you select the nonblank cells first, and then copy and paste them.

Kutools for Excel, with more than
300
 handy functions, makes your jobs more easier. 
Free Download
free full-featured in
30
days

After free installing Kutools for Excel, please do as below:

1. Select the cell range, and click Kutools > Select > Select Nonblank Cells. See screenshot:
doc select nonblank cell 2

2. Then the nonblank cells are selected, press Ctrl + C to copy them, then go to select a cell where you want to output the paseted result, press Ctrl + V to paste selected nonblank cells. See screenshot:
doc select nonblank cell 3

arrow blue right bubble Copy and paste non-blank cell only

Kutools for Excel:
300
+ functions you must have in Excel,
30
-day free trial from here

The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by
80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than
    300
    powerful features    . Supports Office/Excel
    2007-2019 and 365
    . Supports all languages. Easy deploying in your enterprise or organization. Full features
    30
    -day free trial. 60-day money back guarantee.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by
    50%
    , and reduces hundreds of mouse clicks for you every day!
officetab bottom
Comments (11)
No ratings yet. Be the first to rate!
etchvee
etchvee
This comment was minimized by the moderator on the site
thanks for the super formula: =LOOKUP("zzzzz",CHOOSE({1,2},"",INDEX(A:A,SMALL(IF($A$1:$A$15"",ROW($A$1:$A$15)),ROWS($B$1:B1)))))
Reply
0
0
em07
em07
This comment was minimized by the moderator on the site
This formula is absolutely amazing, thank you. Unfortunately it breaks when trying to copy from another sheet, is there a workaround?
Reply
0
0
abc
abc     em07
This comment was minimized by the moderator on the site
correct the references when using the formula between different sheets. It does work. =LOOKUP("zzzzz",CHOOSE({1,2},"",INDEX('List of Overdue items'!A:A,SMALL(IF('List of Overdue items'!A$1:A$1500"",ROW('List of Overdue items'!A$1:A$1500)),ROWS('List of Overdue items'!B$1:B2)))))
Reply
0
0
Hellooo
Hellooo
This comment was minimized by the moderator on the site
Is there a way to make the macro work when referencing between different sheets?
Reply
0
0
Quintin
Quintin
This comment was minimized by the moderator on the site
Hi #abc I have used your formula =LOOKUP("zzzzz",CHOOSE({1,2},"",INDEX('List of Overdue items'!A:A,SMAL L(IF('List of Overdue items'!A$1:A$15 00"",ROW('List of Overdue items'!A$1:A$15 00)),ROWS('List of Overdue items'!B$1:B2)) ))) I have data in one Worksheet and want to copy that data into another worksheet (same doc). Can you help?
Reply
0
0
cvh
cvh
This comment was minimized by the moderator on the site
These tools delete the blank cells and compress the row. I want the format to be retained, including the spaces, but I do not want empty cells to overwrite cells containing existing data. i.e. I want to add the new, copied, cells in a column onto an existing column.
Reply
0
0
Peter
Peter
This comment was minimized by the moderator on the site
When i select and copy only non-blank cells, how can i then paste it somewhere else with keeping the same empty ranges between them?
Reply
0
0
Mickael
Mickael
This comment was minimized by the moderator on the site
Thanks for the great tips! I am using the Find & Select feature, but it's taking a long time because my spreadsheet contains 70 columns and 450k rows. Is there a way to fasten the process?
Reply
0
0
Sandip
Sandip
This comment was minimized by the moderator on the site
Not working on Numbers !!!
Works fine on text !!!
Please guide
Reply
0
0
Sandip Kudalkar
Sandip Kudalkar
This comment was minimized by the moderator on the site
Array formula Works fine on text
but don't work on numbers.
Reply
Report
0
0
Alif
Alif
This comment was minimized by the moderator on the site
=Filter(A:A,A:A<>"")
Reply
0
0
There are no comments posted here yet
Leave your comments
Guest
Posting as Guest
Login
×
Rate this post:
Reset
0   Characters
Suggested Locations

Follow Us

Copyright © 2009 - www.extendoffice.com. | All rights reserved. Powered by ExtendOffice. | Sitemap
Microsoft and the Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries.
Protected by Sectigo SSL