sandbagger
03-15-2009, 02:17 PM
coming into my function is a 2d array. there will be a varying number of empty rows the end of the array. i want to delete all trailing empty rows. for this application, it is safe to assume if the first cell in a row is empty the entire row can be deleted.
the problem is that when i define trimmedArray below i get an error: "Constant expression required". Looks like "redim preserve" won't work since i need to change the 1st dimension of the array.
what is the most efficient way to do this?
Function trimArray(a As Variant)
Dim i As Integer, emptyRowCnt As Long
i = UBound(a, 1)
emptyRowCnt = 0
Do While a(i, LBound(a, 2)) = ""
emptyRowCnt = emptyRowCnt + 1
i = i - 1
Loop
Dim trimmedArray(UBound(a, 1) - emptyRowCnt)
For i = LBound(a, 1) To UBound(a, 1) - emptyRowCnt
trimmedArray(i) = Application.WorksheetFunction.Index(a, i)
Next i
trimArray = trimmedArray
End Function
the problem is that when i define trimmedArray below i get an error: "Constant expression required". Looks like "redim preserve" won't work since i need to change the 1st dimension of the array.
what is the most efficient way to do this?
Function trimArray(a As Variant)
Dim i As Integer, emptyRowCnt As Long
i = UBound(a, 1)
emptyRowCnt = 0
Do While a(i, LBound(a, 2)) = ""
emptyRowCnt = emptyRowCnt + 1
i = i - 1
Loop
Dim trimmedArray(UBound(a, 1) - emptyRowCnt)
For i = LBound(a, 1) To UBound(a, 1) - emptyRowCnt
trimmedArray(i) = Application.WorksheetFunction.Index(a, i)
Next i
trimArray = trimmedArray
End Function