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:
See https://newtonexcelbach.wordpress.com/2011/05/10/using-array-formulas/ if you are not sure about using array functions.
Or sort the range on Column A and copy the non-blank cells to the new location.
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.
Pingback: Daily Download 33: Miscellaneous | Newton Excel Bach, not (just) an Excel Blog