Copy and Paste Only Non-blank Cells

This post will guide you how to copy and paste only non-blank cells in Excel 2013/2016. How do I copy only non blank cells with VBA code in Excel. Or how to get non-blank cells from a range of cells with a formula in Excel.

This post will introduce four methods to copy and paste only non-blank cells in a selected range in Excel.

Method1: Copy non-blank cells with Filter feature


If you want only copy non-blank cells in a range in Excel, you need to select the non-blank cells firstly, then press Ctrl +C keys to copy the selected cells. So how to only select all non-blank cells in the selected range in your worksheet. Just do the following steps:

#1 select the range of cells that you want to filter all non-blank cells.

copy and paste non-blank cells1

#2 go to DATA tab, click Filter command under Sort & Filter group.

copy and paste non-blank cells2

#3 the drop-down button should appear in the first cell of your range. Click on this drop down button. de-select the (Blanks) option and then click on the OK button.

copy and paste non-blank cells3

#4 you will see that only non-blank cells are displayed in the selected range.

copy and paste non-blank cells4

#5 then you can select all non-blank cells in the current range and press Ctrl + C shortcuts in your keyboard, and then press Ctrl + V keys to paste the selected cells into a destination cell.

copy and paste non-blank cells5

Method2: Copy non-blank Cells with Go To Special Feature


You can also use the Go To Special feature to select all non-blank cells in the selected range of cells. Do the following steps:

#1 select the range of cells that you want to filter all non-blank cells.

copy and paste non-blank cells1

#2 go to HOME tab, click Find & Select command under Editing group. And select Go To Special from the popup menu list. The Go To Special window will open.

copy and paste non-blank cells6

#3 select Constants radio button in the Select section under Go To Special dialog. Then click OK button.

copy and paste non-blank cells7

#4 all non-blank cells in the selected range are highlighted.

copy and paste non-blank cells8

#5 you can press Ctrl + C shortcuts to copy cells have been highlighted, and then press Ctrl + V keys to paste the selected cells into a destination cell.

copy and paste non-blank cells9

Method3: Copy non-blank Cells with VBA Macro


You can also use an Excel array formula based on the LOOKUP function, the CHOOSE function, the INDEX function, the SMALL function, the IF function, the ROW function and the ROWS function to extract all non-blank cells from a cell. Like this:

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

Type this formula into cell B1, and then press Ctrl +Shift +Enter shortcuts to change this formula as array formula. And then drag the AutoFill Handle from Cell B1 to B9.

copy and paste non-blank cells10

All non-blank cells from the range A1:A9 are extracted in the range B1:B9.

Method4: Copy non-blank Cells with Formula


You can also write an Excel VBA macro to copy and paste all non-blank cells, just do the following steps:

#1 select the range of cells that you want to filter all non-blank cells

copy and paste non-blank cells11

#2 open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Get the position of the nth using excel vba1

#3 then the “Visual Basic Editor” window will appear.

#4 click “Insert” ->”Module” to create a new module.

convert column number to letter3

#5 paste the below VBA code into the code window. Then clicking “Save” button.

copy and paste non-blank cells14

Sub CopyPasteNonBlankCells()
    Application.Selection.SpecialCells(xlCellTypeConstants).Copy Destination:=Range("B1")
End Sub

#6 back to the current worksheet, then run the above excel macro. Click Run button.

copy and paste non-blank cells13

#7 let’s see the result.

copy and paste non-blank cells12

Related Functions


  • Excel LOOKUP function
    The Excel LOOKUP function will search a value in a vector or array.The LOOKUP function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the LOOKUP function is as below:= LOOKUP (lookup_value, lookup_vector, [result_vector])…
  • Excel Choose Function
    The Excel CHOOSE function returns a value from a list of values. The CHOOSE function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the CHOOSE function is as below:=CHOOSE (index_num, value1,[value2],…)…
  • Excel INDEX function
    The Excel INDEX function returns a value from a table based on the index (row number and column number)The INDEX function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the INDEX function is as below:= INDEX (array, row_num,[column_num])…
  • Excel ROWS function
    The Excel ROWS function returns the number of rows in a cell reference.The syntax of the ROWS function is as below:= ROWS(array)…
  • Excel SMALL function
    The Excel SMALL function returns the smallest numeric value from the numbers that you provided. Or returns the smallest value in the array.The syntax of the SMALL function is as below:=SMALL(array,nth) …
  • Excel ROW function
    The Excel ROW function returns the row number of a cell reference.The ROW function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the ROW function is as below:= ROW ([reference])….
  • Excel IF function
    The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….

Comments

So empty here ... leave a comment!

Leave a Reply

Your email address will not be published. Required fields are marked *

Sidebar