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

See https://newtonexcelbach.wordpress.com/2011/05/10/using-array-formulas/ if you are not sure about using array functions.