1

How can I append two dynamic arrays with the same dimension and then sort it in ascending order of those values?

For example,

Dim a as Variant
a = sheets(1).range("B" & firstRow & ":B" & lastRow)
Dim b as Variant
b = sheets(1).range("C" & firstRow & ":C" & lastRow)

firstRow and lastRow are properly defined.

My question is how can I append array b after array a. Let us assume a = (8,3,5) and b = (1,7,2). Array c must be (8,3,5,1,7,2). I tried the following with no success. Can anyone explain why it's not working?

Dim c as variant
c = Join(b, Join(a, ","))

To sort the values introduced in array c, I have to create a function or can do in only one operation?

Thank you

LAST VERSION

I have changed to the following code:

Dim aArray() As variant
aArray = aRange
Dim bArray() As Variant
bArray = bRange
Dim cArray() As Variant
cArray = aArray
Dim i As Integer

For i = 1 To UBound(coutArray)
 ReDim Preserve cArray(UBound(aArray)+1) As Variant
 cArray(i + UBound(aArray),1) = bArray(i,1)
Next i

I am trying to append cArray() in the end of aArray where these two arrays are going to be introduced in cArray(). Why this is not working? I would like to understand.

I also tried by creating a function (How do I Merge two Arrays in VBA?) but then I how will I call into a procedure? It was not working as well.

Thanks!

Community
  • 1
  • 1
codeLearner
  • 86
  • 4
  • 14

1 Answers1

0

Do you want to append arrays or the excel ranges?

a and b are in your sample only Excel Range objects. Not arrays.

Anyway: Heres a good article for joining arrays: How do I Merge two Arrays in VBA?

And here an article for sorting: VBA array sort function?

Community
  • 1
  • 1
Shmukko
  • 572
  • 3
  • 11
  • Actually a and b has the values of excel ranges. I thought I had to convert the ranges into arrays before looking for values of an array. – codeLearner Feb 07 '17 at 19:39
  • I really don'Tget what you want to do. Join annotates all elements of an sinhgle dimension array to a string. e.g. a = (8,6,9) Join(a, "/") Returns a String "8/6/9" – Shmukko Feb 07 '17 at 19:48
  • I want to create two arrays from two different columns. And then, I need to aggregate these two arrays into a third one. For example, considering a = Array(8,3,5) and b = Array(1,72) the third array c = Array(1,2,3,5,7,8) where the values are sorted in ascending order. – codeLearner Feb 07 '17 at 20:09
  • OK I added some links to the answer. – Shmukko Feb 07 '17 at 20:14