0

I have a named range which has been placed into an array; the array have 10 fixed columns, but any number of rows. The code to this point is:

Dim LD As Long
Dim Rng As Excel.Range
Dim vArray() As Variant
Dim varTOne() As Variant
Dim varTTwo() As Variant
Dim DSheet As Worksheet
Set DSheet = Worksheets("DataSheet")

LD = DSheet.Cells(Rows.count, "A").End(xlUp).row

Set Rng = ThisWorkbook.Worksheets("DataSheet").Range("A5:J" & LD)
vArray = Rng

Two of the columns, A and B say, are used to uniquely identify a certain result, which is in the last column (J). I need to first ensure that the result is indeed in the array (existence) and then extract the result. In order to ensure existence I would like to merge these two columns A and B, which will give me a "unique key" and match this key to a list. This would we quicker than a nested loop though Column A and B! What is the best way to go around this? I managed to split off the two columns like this:

 varTOne = Application.Index(vArray, , 1)
 varTTwo = Application.Index(vArray, , 2)

But some how cant get them to merge properly using any of How do I Merge two Arrays in VBA? What am I doing wrong here?

Is it possible to merge two columns in an array without some timely loop?

  • Timely? Array loops are fast if there isn't some crazy nested bad logic going on. – QHarr Feb 09 '18 at 14:54
  • 1
    You can simply concatenate the two values for the same row in the same loop with myArr(i,1) & myArr(i,2) or + depending on contents. – QHarr Feb 09 '18 at 14:56
  • timely in the sense that it is a matter of find one string instead of finding one and then another in a nested loop. Of course its possible, just though this method would be quicker perhaps? –  Feb 09 '18 at 14:56
  • Yes, thanks, just though there might be a quicker way. –  Feb 09 '18 at 14:58
  • 1
    I believe .Index to slice an array slows things down but I am not an expert. I think there maybe limits also on size. – QHarr Feb 09 '18 at 14:59
  • hmmm, yes your correct there! –  Feb 09 '18 at 15:05

0 Answers0