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 Kutools for Excel 
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 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.
2. Then click Home > Find & Select > Go To Special, see screenshot:
3. In the Go To Special dialog box, check Constants option, see screenshot:
4. Then click OK, and only the value cells have been selected in the list.
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:
Note: This method only be available for the constants, it is not applied to the formula cells.
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.
After free installing Kutools for Excel, please do as below:
1. Select the cell range, and click Kutools > Select > Select Nonblank Cells. See screenshot:
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:
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:
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:
4. And then click OK, all the non-blank cells have been filtered out as following screenshot shown:
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.
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)))))
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:
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.
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:
4. And then click OK, another prompt box will pop out to let you choose a cell to put the data.
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.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
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!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in