arrays - 使用VBA筛选出阵列中的空单元格

我试图做一个函数，它接受一维数组，由空单元格过滤掉，然后浓缩数组并返回它。

示例：[1] [2] [3] [“”] [4]返回[1] [2] [3] [4]

我不断得到#Value！当我尝试通过index（）调用此新数组时。

Function BlankRemover(ArrayToCondense As Variant) As Variant

Dim ArrayWithoutBlanks() As Variant
Dim CellsInArray As Long
Dim ArrayWithoutBlanksIndex As Long

ArrayWithoutBlanksIndex = 1

    For CellsInArray = LBound(ArrayToCondense) To UBound(ArrayToCondense)

        If ArrayToCondense(CellsInArray) <> "" Then

        ArrayWithoutBlanks(ArrayWithoutBlanksIndex) = ArrayToCondense(CellsInArray).Value

        ArrayWithoutBlanksIndex = ArrayWithoutBlanksIndex + 1

        End If

    Next CellsInArray

ReDim Preserve ArrayWithoutBlanks(LBound(ArrayToCondense) To ArrayWithoutBlanksIndex)

ArrayWithoutBlanks = Application.Transpose(ArrayWithoutBlanks)
BlankRemover = ArrayWithoutBlanks

End Function

最佳答案

试试这个：

Function BlankRemover(ArrayToCondense As Variant) As Variant()

Dim ArrayWithoutBlanks() As Variant
Dim CellsInArray As Variant
ReDim ArrayWithoutBlanks(1 To 1) As Variant
For Each CellsInArray In ArrayToCondense
    If CellsInArray <> "" Then
        ArrayWithoutBlanks(UBound(ArrayWithoutBlanks)) = CellsInArray
        ReDim Preserve ArrayWithoutBlanks(1 To UBound(ArrayWithoutBlanks) + 1)
    End If
Next CellsInArray

ArrayWithoutBlanks = Application.Transpose(ArrayWithoutBlanks)
BlankRemover = Application.Transpose(ArrayWithoutBlanks)

End Function