Hide #N/A in an Array? ISNA isn't working

[RumJellybean]

I am using a formula to gather data from specified "DATA ENTRY" cell ranges(sheet1!a1:a30) and (sheet2!a1:a30), Into one "SUMMARY" range (sheet6!a1:a60). The cells in the "DATA ENTRY" ranges will have varying ammounts of data input into them. In combination, I am also using a User-Defined function named "NoBlanks" that eliminates blanks in the information that is gathered from the entry ranges.

I select the summary range and use *shift+ctrl+enter* to imbed the formula below, which works just fine.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
=noblanks(Sheet1!A1:A30,Sheet2!A1:A30)<o></o>

This user function gets rid of the blanks for me.
Option Base 1
Function NoBlanks(ParamArray rgs()) As Variant
Dim v() As Variant
Application.Volatile True
For i = LBound(rgs) To UBound(rgs)
For j = 1 To Application.CountA(rgs(i))
num = num + 1
ReDim Preserve v(num)
v(num) = rgs(i)(j)
Next
Next
NoBlanks = Application.Transpose(v)
End Function <o></o>
The problem is that if any data entry cells are empty, I get the good ole *n/a error on the summary sheet. I tried the classic:
=if(isna(noblanks(Sheet1!A1:A20,Sheet2!A1:A20)),"",noblanks(Sheet1!A1:A20,Sheet2!A1:A20))
Also I tried conditional formatting via the "Use formula" option with this =isna(Sheet1!A1:A20,Sheet2!A1:A20) and changed the format to white text. It did not work either and i'm supposing it has something to do with it being an array. Any suggestions?

 

[Colin Legg]

You could size the array your function returns to match the dimensions of the caller range, and just leave excess elements (not needed due to blanks) empty at the end of the array. As you pointed out, if the caller range is larger than your return array, you get #N/A values for elements out of the array scope.

Have a look at this link:
http://www.cpearson.com/excel/returningarraysfromvba.aspx


Also, you are correctly passing all ranges into the function's parameter list, so it looks to me that you shouldn't need to make the UDF volatile.

Hope that helps

 

[RumJellybean]

Thank you for the link. I'm trying to collect as much info as possible on excel. However, I actually need the return ranges structured the way they are. I finally got Conditional formatting to handle it. This is an old shabby way to do it but:
I selected the caller range (=$C$2:$C$30), and for the formula tried
=isna(c2) (instead of the whole range) and changed the text color to the same as the background color.

Problem Solved!!!