Copy non-blank rows to another sheet

A thread at Eng-Tips asked for a way to display the data from Sheet2 on Sheet1, but only the rows that were not blank in Column A.

There are probably ways to do it without VBA, but it seems to me that the simplest and most efficient way is to write a User Defined Function (UDF) that returns an array with the required data.  The procedure is:

  • Read the data from the specified range on Sheet2 into a VBA variant array.
  • Create a new array of the same size.
  • Copy the data from the imported array into the new array, skipping the blank lines.
  • Write a blank (“”) into any spare rows at the bottom of the new array, so they don’t display as 0.

Here is the code:

Function NonBlanks(DataRange As Variant) As Variant
Dim i As Long, j As Long, NumRows As Long, NumCols As Long, RtnA() As Variant
Dim RtnRow As Long

If TypeName(DataRange) = "Range" Then DataRange = DataRange.Value2
NumRows = UBound(DataRange)
NumCols = UBound(DataRange, 2)

ReDim RtnA(1 To NumRows, 1 To NumCols)
For i = 1 To NumRows
If DataRange(i, 1) <> "" Then
RtnRow = RtnRow + 1
For j = 1 To NumCols
If DataRange(i, j) <> "" Then RtnA(RtnRow, j) = DataRange(i, j) _ 
Else RtnA(RtnRow, j) = ""
Next j
End If
Next i

For i = RtnRow + 1 To NumRows
For j = 1 To NumCols
RtnA(i, j) = ""
Next j
Next i

NonBlanks = RtnA
End Function

You can download a file with the NonBlanks function from: NonBlank.xlsb

And here is what it looks like:

NonBlank function

NonBlank function

See if you are not sure about using array functions.

This entry was posted in Arrays, Excel, UDFs, VBA and tagged , , , , . Bookmark the permalink.

3 Responses to Copy non-blank rows to another sheet

  1. David P. Evers says:

    Or sort the range on Column A and copy the non-blank cells to the new location.


    • dougaj4 says:

      That’s probably the easiest way for a one-off, but the Eng-Tips thread was looking for a solution that would instantly transfer any changes on Sheet 2 back to Sheet 1.


  2. Pingback: Daily Download 33: Miscellaneous | Newton Excel Bach, not (just) an Excel Blog

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.