PDA

View Full Version : Solved: delete trailing empty rows from 2d array



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

mdmackillop
03-15-2009, 03:25 PM
You can only redim the last dimension ie 10,3 to 10,2. You cannot trim 10,3 to 9,3. A workaround is to use a 3,10 array to contain your data and then resize and transpose if required.

sandbagger
03-15-2009, 04:58 PM
good idea with transpose. this seemed to work:
1. transpose array
2. redim array, trimming "emptyRowCount" columns
3. transpose array back

thank you.