Arrays and Concatination

[wilsjohn]

Hi I am trying to create an array from an excel spreadsheet then manipulate it for example say you have cells a1:a5 with different values or strings I want to take that and then create a array so that I can then make there be extra rows between each string, add new stuff between for example say I have A1 Total revenue A2 COGS after I have created the array with these two strings I would like to Create a row between and Total revenue and COGS to create Revenue growth rate for example. This is just a general overview of where I want to go. However, I want the code to be dynamic, below is what I have written so far for selecting a range to create the array as list (I believe this doesn't actually count as an array? but it seems to suit my purpose). At the moment I am trying to make it print below but as far as my current method words I need to know the exact length of the variable List in order for this to work. So I am trying to make it dynamic by allowing a6: nrng but I cant make this work as I am not concatinating a with 8 (6 + 3 (a1:a3) - 1) when I do "a" it becomes "a8" which I think breaks the script.

So How do I concatenate this properly so that nrng becomes a8 so that range("a6a8") = List works?



Dim List() As Variant
List = Range("A1:A3").Value2


Countrng = Application.WorksheetFunction.CountA(Range("A1:A3"))
nrng = a & (6 + Countrng - 1)
[a10] = nrng
Range("a6:&nrng") = List

 

[Fluff]

How about

   Dim Ary As Variant
   
   Ary = Range("A1" & Range("A" & Rows.Count).End(xlUp)).Value2

 

[wilsjohn]

Didnt work, does it need a : ?

 

[Fluff]

Apologies, there's a typo it should be

Ary = Range("A1" [COLOR=#ff0000],[/COLOR] Range("A" & Rows.Count).End(xlUp)).Value2