Speeding up array functions by shortening the ranges. Referencing the last row in the range to a cell calculating it.

[dakota727]

I am using the following array function to look up data matching several criteria:

{=INDEX('Inst Data'!$A$1:$H$1500,MATCH(1,('Inst Data'!$A$1:$A$1500=$B7)*('Inst Data'!$D$1:$D$1500=AA$1)*('Inst Data'!$F$1:$F$1500=""),0),8)}

In order to minimize the number of calculations the spreadsheet has to chug through I wanted to determine the number of rows on the Inst Data tab. For now I have it listed as 1500 but what I am trying to do is replace the 1500 with the reference to the value in cell Z1. In Z1 I was using a CountA() function to determine the number of rows of data once the data file is imported into my spreadsheet on the tab 'Inst Data'.

I have tried a couple of things (concatenating the whole reference to the range including the sheet name, etc.) but so far no luck.

I have about 900 of these look up arrays so I was trying to make the range as small as possible and not have to over estimate the number of rows in my data since it will change with every instrument run.

Any ideas would be appreciated,

Thanks

 

[offthelip]

I would do that using VBA which would be much faster (it would take milliseconds). Load the whole range A1:H1500 into a variant array, the find the matching line for each row using something like the code below , then find the value from the A1H1500 array and write the result out to anothe variant array.
Remember every access to the worksheet takes at least 30 milliseconds, in the same time I would estimate you can do the whole calcuation for every row if yoiu do it using variant arrays entirely in VBA.

lastA = Cells(Rows.Count, "A").End(xlUp).RowArr = Range(Cells(1, 1), Cells(lastA, 1))
Drr = Range(Cells(1, 4), Cells(lastA, 4))
Frr = Range(Cells(1, 6), Cells(lastA, 6))
Adata = Cells(1, 8)
Ddata = Cells(1, 9)
fdata = Cells(1, 10)


For i = 1 To lastA
  If Arr(i, 1) = Adata And Drr(i, 1) = Ddata And Frr(i, 1) = fdata Then
   matchd = i
   
  End If
Next i

 

[Rick Rothstein]

I would do that using VBA which would be much faster (it would take milliseconds). Load the whole range A1:H1500 into a variant array, the find the matching line for each row using something like the code below , then find the value from the A1H1500 array and write the result out to anothe variant array.
Remember every access to the worksheet takes at least 30 milliseconds, in the same time I would estimate you can do the whole calcuation for every row if yoiu do it using variant arrays entirely in VBA.

[B][COLOR="#FF0000"]lastA = Cells(Rows.Count, "A").End(xlUp).RowArr = Range(Cells(1, 1), Cells(lastA, 1))[/COLOR][/B]
[B][COLOR="#0000FF"]Drr = Range(Cells(1, 4), Cells(lastA, 4))
Frr = Range(Cells(1, 6), Cells(lastA, 6))[/COLOR][/B]
Adata = Cells(1, 8)
Ddata = Cells(1, 9)
fdata = Cells(1, 10)

For i = 1 To lastA
  If Arr(i, 1) = Adata And Drr(i, 1) = Ddata And Frr(i, 1) = fdata Then
   matchd = i
   
  End If
Next i

On my screen your first two lines of code ended up on a single line (highlighted in red above). The OP should replace what I highlighted in red above with this...

lastA = Cells(Rows.Count, "A").End(xlUp).Row
Arr = Range(Cells(1, 1), Cells(lastA, 1))

I would also note that the two lines of code I highlighted in blue above can be simplified somewhat (there is no need to use the Cells object inside a Range object call when the reference is a fixed cell)...

Drr = Range("D1", Cells(lastA, 4))
Frr = Range("F1", Cells(lastA, 6))

 

[offthelip]

@ Rick,
thanks for spotting the cut and paste error which concatenated the first two lines, I have had that problem before.
In terms of simplifying the range addressing , I prefer the simple numerical method it makes it very clear how many columns are in the array , if one is going to use columns letters then I think:

drr = Range("D1:D" & lastA)

is better since it avoids the mixed addressing modes within one statement.

 

[Rick Rothstein]

@ Rick,
thanks for spotting the cut and paste error which concatenated the first two lines, I have had that problem before.
In terms of simplifying the range addressing , I prefer the simple numerical method it makes it very clear how many columns are in the array , if one is going to use columns letters then I think:

drr = Range("D1:D" & lastA)

is better since it avoids the mixed addressing modes within one statement.

{Personal preference} I try to avoid making VB do concatenations whenever possible as that is a somewhat slow process for it to do (it has to find a new contiguous memory location in which to dump the substrings being concatenated into, in the proper order, and then modify the memory location referencing the concatenation). Of course I don't know that using a string and Cells reference is actually faster, it's just that avoiding the concatenation whenever possible tends to be an efficient course of action as an overall strategy.

 

[MARK858]

@ Rick,
thanks for spotting the cut and paste error which concatenated the first two lines, I have had that problem before.

Did you click the # icon then copy inside the tags? It seems to happen when you do that rather than pasting the code, selecting it then clicking the # icon.

 

[offthelip]

Did you click the # icon then copy inside the tags? It seems to happen when you do that rather than pasting the code, selecting it then clicking the # icon.

Yes , I did. thanks for the tip , I will try to avoid doing that.