Well, thats cheating Tusharm!
Yours is fastest by a long shot. That is awesome and I've never seen that in VBA before...good to know. But you are ACCESSING the value not building the array.
Although you void building the 1-d array, which saves time, my main concern is the time it takes to ACCESS the values. Your method of accessing the values take longer because of the calc's in the properties. I explained a few posts ago that I access this array millions of times, but I only build the array a few thousand times. So the real time saver will be in mimizing access time using a 1-d array. However to do this, I do need to build the 1-d from my table of values so I can access the values very fast later. Hope that makes sense. That Properties thing reminds me of C++...didn't know you could do it in VBA.
Anyway, if there is an faster way to build a 1-d array from data that looks like this in Excel (such that the 1-d array elements look like this a,b,c,d,e), please post: (for the fastest method so far see the code at the bottom of this post...it uses data that starts in column 3)
a d
b e
c
Norie - I spent an hour composing a really long post in that other thread detailing the entire process (at your request). I don't know what more I need to do. The data is all accessed from different excel workbooks so I use With blocks a lot. Its too complex to explain more I think. So I am asking specific questions.
Code:
Function TableToArray(wb As String, sht As Integer) As String()
With Workbooks(wb).Sheets(sht)
Dim d2Array As Variant
d2Array = .Range("C1", FindLastCell())
ReDim d1Array(Application.CountA(.Cells)) As String
'MsgBox UBound(d1Array)
Dim idx As Integer
'MsgBox LastRowInOneColumn(3, wb, sht) & " rows"
For j = 1 To Application.CountA(.Columns)
For k = 1 To LastRowInOneColumn(3, wb, sht)
idx = idx + 1
If d2Array(k, j) = "" Then
GoTo done
End If
d1Array(idx) = d2Array(k, j)
'MsgBox idx & " =idx k= " & k & " j= " & j
Next k
Next j
done:
End With
TableToArray = d1Array
End Function
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function LastRowInOneColumn(xcol As Long, wb As String, sht As Integer) As Long
'Find the last used row in a Column: column A in this example
Dim xLastRow As Long
With Workbooks(wb).Sheets(sht)
xLastRow = .Cells(.Rows.count, xcol).End(xlUp).row
End With
LastRowInOneColumn = xLastRow
End Function
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function FindLastCell() As Range
Dim theLastColumn As Integer
Dim theLastRow As Long
Dim theLastCell As Range
With ActiveSheet
If WorksheetFunction.CountA(Cells) > 0 Then
'Search for any entry, by searching backwards by Rows.
theLastRow = .Cells.Find(What:="*", after:=[a1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row
'Search for any entry, by searching backwards by Columns.
theLastColumn = .Cells.Find(What:="*", after:=[a1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
Set FindLastCell = .Range(Cells(theLastRow, theLastColumn).Address)
End If
End With
End Function
