Trouble with NoBlanks() function

[N1Martian]

I am using the noblanks() function from this site in order to remove blanks between cells
I then modified it to this:

Function NoBlanks(RR As Range) As Variant
    Dim Arr() As Variant
    Dim R As Range
    Dim N As Long
    Dim L As Long
    
    'check for 1D Range
    If RR.Rows.Count > 1 And RR.Columns.Count > 1 Then
        NoBlanks = CVErr(xlErrRef)
        Exit Function
        End If
    
    'don't know what app.caller does
    If Application.Caller.Cells.Count > RR.Cells.Count Then
        N = Application.Caller.Cells.Count
        Else
        N = RR.Cells.Count
        End If
    
    'build array by individual cells
    ReDim Arr(1 To N)
    N = 0
    For Each R In RR.Cells
        If Len(R.Value) > 0 Then
            N = N + 1
            Arr(N) = R.Value
        End If
    Next R

    'modification from original here to shorten array to min length
    ReDim Preserve Arr(1 To N)
    
    'return value
    If Application.Caller.Rows.Count > 1 Then
        NoBlanks = Application.Transpose(Arr)
        Else
        NoBlanks = Arr
        End If
End Function

Here's the spreadsheet. The range I'm using is the left most column.

The trouble I'm having is:

1) If break on the last line in order to debug, I have Arr() and NoBlanks() defined as 3-row arrays. In the debug window ?Arr(1) works just fine, but ?NoBlanks(1) returns a compile error from 'type mismatch'

2) How can I display =NoBlanks(namedrange) in a cell. I've tried CTRL/SHIFT/ENTER, but I can only see one value.

3) My main goal in all of this was to put this array into a data validation list, but putting =NoBlanks(namedrange) hasn't worked.

Questions 1 and 2 are a bit superfluous to my final goal, but I'm really trying to learn this stuff.

Any advice? Thanks.

 

[Andrew Poulsom]

You have to enter the formula in a range of cells at the same time (large enough for all the returned results to fit). Then you can use that range in your data validation.