Adding to the end of an array

Hi Diane,

I hope you are fine.

You will need a different approach to do what you want.

I am not sure this is the most efficient solution, but it is the only one came to my mind. I will come back to you, if I come up with another solution.

Sub RunConsolidateNames()

Dim Sheet6Array as variant
Dim Sheet7Array as variant
Dim Sheet8Array as variant
Dim Sheet9Array as variant
Dim AllArray as Variant
Dim longCount as Long

Sheets(6).Select
Sheet6Array=range("A2",range("a2").end(xlDown))
Sheets(7).Select
Sheet7Array=range("A2",range("a2").end(xlDown))
Sheets(8).select
Sheet8Array=range("A2",range("a2").end(xlDown))
Sheets(9).Select
Sheet9Array=range("A2",range("a2").end(xlDown))

Redim AllArray(1 to ubound(Sheet6Array,1)+1 to ubound(Sheet7Array,1)+1 to ubound(Sheet8Array,1)+1 to ubound(Sheet9Array,1),1)

For longCount=1 to ubound(Sheet6Array,1)
AllArray(longCount,1)= Sheet6Array(longCount,1)
Next longCount

For longCount=1 to ubound(Sheet7Array,1)
AllArray(longCount+ubound(Sheet6Array,1),1)= Sheet7Array(longCount,1)
Next longCount

For longCount=1 to ubound(Sheet8Array,1)
AllArray(longCount+ubound(Sheet6Array,1)+ubound(Sheet7Array,1),1)= Sheet8Array(longCount,1)
Next longCount

For longCount=1 to ubound(Sheet9Array,1)
AllArray(longCount+ubound(Sheet6Array,1)+ubound(Sheet7Array,1)+ubound(Sheet8Array,1),1)= Sheet9Array(longCount,1)
Next longCount

End Sub



Please let me know if you get an error.



Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector