VBA - copy 2-dimensional array into 1-dimensional (single column)

astrbac

Board Regular
Joined
Jan 22, 2015
Messages
55
Hello,

I have a (probably) simple problem but could not solve it for the life of me. I need to copy 2-dimensional array from one sheet into a single column on another sheet.

Example:

Sheet1, data is in a range C2:N225

needs to be copied (transposed) onto

Sheet2, in a range D2:D2680

> First row of the array (Sheet1!C2:N2) goes into Sheet2!D2:D14
> Second row (Sheet1!C3:N3) goes into Sheet2!D15:D27
> ... and so forth

Many thanks!
 
Hi

I'd start by the formulas. I think you'll find the formulas simple.

In this case, since you want to build a 1D array out of a rectangular range, you have to find for each position in the array what's the row and column of the corresponding value in the source range.

Ex.
Let's say you have a rectangular range 10 rows x 12 columns.

If you start at the top left and go left and then to then down, the 30th element of the array is in row 3, column 6 (2 whole rows + 6 columns).

You get the coordinates of the 30th element, row and column
row: INT(30/12)
column: MOD(30,12)

or, in general

row: INT(order of the element/number of columns of the range)
column: MOD(order of the element, number of columns of the range)

You can see that those are the formulas that I used.

The difference is that I used for the order of the element an array instead of just one position.

In the case of this example if I use

for the row: INT({1,2,3,4, ..., 240}/12)
for the column: MOD({1,2,3,4, ..., 240},12)

I get the coordinates,row and column in the source range, for all the elements of the array.

I can then use them in INDEX() to build the 1D array that I use in the 1 liner and write the array in the destination range.

Hope it's clear.

Remark: this is not exactly what happens, sometimes you may have to add or subtract 1 to account for the last or first element of a row, but this is how you can approach the problem.
 
Last edited:
Upvote 0

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Hi

I'd start by ........


@pgc

Great, pgc
that was very helpful. It is seeming quite understandable now ( like most things do with hindsight) ( at least the getting of the indicies...#### ) , but it would have taken me ages to get the point without your help. Thanks very much.
So:

_1 ) ( Minor typo I think at the start, you meant from Top left, going left to right, then top to bottom, like the Cells(x) convention from the ask2tsp stuff, Post #3... normal Excel convention I think for this stuff ? )


_2 ) Columns Indicie calculations, clms() Array

_2a ) I stupidly missed the __S__ in columnS and got confused with
My______Clms()=Evaluate("column(C:N)") = Array(3, 4, 5 ....... 14)_____’Gives Array column No.s 3:14
and
Your____ COLUMNS(Sheet1!$C$2:$N$225)_____’Gives column Count in range
=Columns(Sheet1!$C$2:$N$225)
=Columns(Sheet1!$C$2:$N$2)
=Columns(Sheet1!$C$2:$N$34871)
=Columns(Sheet1!$C:$N)
=Columns(Sheet1!$C:$N)
=Columns(Sheet1!$C:$N)
=Columns(C:N)

=12

My fading memory of maths ( especially in my forgotten native English) don’t help either. MOD is “bit left over” as you say. We are “MODin” every number in are range along the “ask2tsp stuff, Post #3” VBA Cells(x) convention, and

_2 b) we do a bit of subtle number juggling to get what shg once explained to me
VBA Column Letter from Column Number. Explained.
as a “not quite right” number system that does not have a 0 in it a “bijective numeration“ thing where no 0 is in ( In our case 1,2,3,4,5,6,7,8,9,10,11,12,1,2,3....

we finally have a Column Array formula like this
=mod(ROW(2:2688)-2,12)+1
Which works in a spreadsheet cell ( at least gives us the first value in the Array, 1, ) but it errors in the VBA Evaluate(“________”) ...... so.......

_2c) So for that problem, then there is the If(Row ___ coercing stuff necessary ( what you and Rory and XLORX discussed to death in that Epic Evaluate Range and VlookUp Thread...) which is required to get the Evaluate(“________”) to give us an Array.


We end up with this Simplified formula
clms() = Evaluate("=if(row(2:2689),mod(ROW(2:2688)-2,12)+1)")
which returns us a 1 Column 2 Dimensional Array which repeats the sequence 1,2,3,4,5,6,7,8,9,10,11,12, 1,2,3,4,5,6,7,8,9,10,11,12, 1,2,3,4,5,6,7,...... etc

_ ...................................................

_ 3) Row indicies
__A similar argument, with your explained INT for the rows gives us this simplified version for the Rows indices
rws() = Evaluate("=if(row(2:2689),INT((ROW(2:2689)-2)/12)+1)")
which returns another 1 Column 2 Dimensional Array which gives this
1,1,1,1,1,1,1,1,1,1,1,1,2,2,2,2,2,2,2,2,2,2,2,3,3,3,3,3,3,3,3,3,3,3,3,4,4........
In words, 12 x 1’s then 12 x 2’s then 12 x 3’s ... etc.


_ 4) Finally Application.Index with these Rows and Columns ( VBA Arrays. )
I see some logic here, ... but must be away now for a bit...I would like to come back in a day of two ( if i can ) when the thing has gone through my head a bit... It may help me to solve another big mystery in this Thre........ Ahh – can’t mention that or I won’t be “allowed” back..........
_ ... I mean finding out exactly how this works....
_ ......”................ use of Application.Index with Look Up Rows and Columns Arguments as VBA Arrays. …..“...#### I think You may be helping me understand this one now.. a bit...finally..

Thanks very much pgc, I think you have really got some things going up in my head and helping it to function a bit just now.....

Alan

Rich (BB code):
Sub pgcDBMOneLinerSHimplifGfied()
Dim rws() As Variant
Dim clms() As Variant
Sheets("Sheet2").Columns(4).ClearContents 'Clear column

rws() = Evaluate("=if(row(2:2689),INT((ROW(2:2689)-2)/12)+1)")
clms() = Evaluate("=if(row(2:2689),mod(ROW(2:2688)-2,12)+1)")

Sheets("Sheet2").Range("D2:D2689").Value = Application.Index(Range("Sheet1!$C$2:$N$225"), rws(), clms())

End Sub
 
Upvote 0
Hi Alan, thanks for the comments. Yes missed the OP requirement for transpose, but that only requires a few additional code words to implement.

Clearing those variables, old habit/good programming practice and read somewhere (would need to find that link again) that certain variables aren't always cleared.

Also in a very recent project at work, some global variables were retaining their previous values, despite a full and clean exit of the procedure; clearing them seem to resolve the problem.
 
Upvote 0
.... Clearing those variables, old habit/good programming practice and read somewhere (would need to find that link again) that certain variables aren't always cleared.
Also in a very recent project at work, some global variables were retaining their previous values, despite a full and clean exit of the procedure; clearing them seem to resolve the problem.

Hijack......

_ . Thanks Jack,
for coming back......
Sort of encourages me to keep to my habits...

_ . Ties up with my ( very limited as yet experience ) . I think in VBA just the Global variables are likely to “live on” after the end of the Sub. But getting into the habit of just doing that a lot anyway is probably good, ( as i use Glob variables a lot ). Good habit like always being very explicit about reference in exactly whether a Range is, even if in most cases in is unnecessary..
_ Just a cases of personal preferences i guess

_ ( If you ever come across that link and have time , can you drop it by here, or whereever - I am not sure if I am right about my only Glob variables bit )
_ ...................

_ I hope if I get time to doing a last follow up to explaining the pgc Code.. That seems an incredibly useful way of “picking out” from one or more Arrays and building up a new one, especially when using the Cells as the first argument in the...._____................ use of Application.Index with Look Up Rows and Columns Arguments as VBA Arrays. …..____...bit
___________arrOut() = Application.Index(arrIn(), rws(), clms())
_ ...( the arrIn() can be replaced with Cells… and can be as big as a XL 2007 + Spreadsheet!!!!! #### )
http://www.mrexcel.com/forum/excel-...y-visual-basic-applications.html?#post4339672
That opens up a lot of possibilities** for doing consolidation of parts of lots of different Arrays that is often required but often ends up in resulting to looping..(Not always__ http://www.mrexcel.com/forum/excel-...y-selected-index-2-d-array-2.html#post4213878 ___ )....( **At least opens up a lot of possibilities when all Arrays are in the same sheet........ easy with XL 2007 and bear in mind the usual size constraint in things like Index seems overcome, partially with the Cells as first Argument in plce of arrIn()
####_____ Trouble writing huge array into worksheet range [SOLVED] - Page 2
)


Alan

P.s.

....missed the OP requirement for transpose, but that only requires a few additional code words to implement......

Yeh, that was no big deal it was your “Slice and Stack” bit that was important alternative form of what the OP wanted... ( I added the one transpose line in my
Sub JackDoneNice()
Post #9
_ ... then did a version of mine which overcame doing an extra line, by modifying the....____
_________arrOut() = Application.Index(arrIn(), rws(), clms())
____________________....bit to come out transposed – another thing demonstrating how powerful that line is, and resulting in my obsession with it just now!?! )



P.P.s. Minor typo in my simplified pgc code, from post bottom of post #12...
clms() = Evaluate("=if(row(2:2689),mod(ROW(2:2688)-2,12)+1)")
should be
clms() = Evaluate("=if(row(2:2689),mod(ROW(2:2689)-2,12)+1)")

Correct code.... ( and a bit more simplified firs, then a bit explained.. )

Rich (BB code):
Sub pgcDBMOneLinerSHimplifGfied_2() ' Post #15   http://www.mrexcel.com/forum/excel-...array-into-1-dimensional-single-column-2.html
Sheets("Sheet2").Columns(4).ClearContents 'Clear column just in case anything there

' Main " One liner" Code, ( Is one code line ):
Worksheets("Sheet2").Range("D2:D2689").Value = _
Application.Index(Range("Sheet1!$C$2:$N$225").Value, _
Evaluate("=if(row(),INT((ROW(2:2689)-2)/12)+1)"), _
Evaluate("=if(row(),mod(ROW(2:2689)-2,12)+1)"))


'End Sub'....Or



'.......


'.......just  to explain a little bit:
Dim rws() As Variant: rws() = Evaluate("=if(row(),INT((ROW(2:2689)-2)/12)+1)")
Dim clms() As Variant: clms() = Evaluate("=if(row(),mod(ROW(2:2689)-2,12)+1)")
Dim arrIn() As Variant: arrIn() = Worksheets("Sheet1").Range("$C$2:$N$225").Value
Dim arrOut() As Variant

Sheets("Sheet2").Columns(4).ClearContents 'Clear column again just to see that next line works

arrOut() = Application.Index(arrIn(), rws(), clms())
Sheets("Sheet2").Range("D2").Resize(UBound(arrOut(), 1), UBound(arrOut(), 2)).Value = arrOut()



End Sub
 
Upvote 0
Hi
A follow up explanation and discussion of the “One Liner method” from Post #8, #10, #11, # 12


We are talking about using a single code line of the following form.......

______arrOut() = Application.Index(arrIn(), rws(), clms())

____....to achieve an Output Array arrOut(), based on a combination of an Input Array arrIn() ( sometimes referred to as a Grid or Matrix or 2D structure ("rows" x "columns"). ), and rws() and clms() which will typically contain a list of "row" and "column" Indices.

_ 1) Basic Simple Form.

Right at the outset a very fundamental point I did not grasp for a long time as I never saw it documented anywhere, is that in the “simplest basic form” VBA pairs up the rws() and clms() indices and returns a ( 1 Dimensional ) Array of Elements .Those Elements are those at the intersect of the indices Pairs.
For this discussion we will note that we can replace arrIn() with a particular Worksheet, ( say
Dim ws as Worksheet: Set ws=Worksheets(“Sheet1”) ), either using
__arrIn()=ws.Cells.Value
or simply referring to the entire Worksheet Range with
__ws.Cells ( noting that by default a reference to a Cell in the Cells Range will return the value in the cell )
The latter version has various advantages that we will discuss later and so we will stick with that for now ###

______arrOut() = Application.Index(ws.Cells, rws(), clms())

Coming back to the “simplest basic form” where i am specifically referring to 1 Dimensional Arrays for the indicies, for example
___rws() = Array(1, 2, 3, 4)
___clms()=Array(1, 2, 3, 3)

____here arrOut() will be a 1 D Array with the values given in the cells A1__B2___C3__and__C4 for convenience call these values __vA1___vB2___vC3__vC4

_ 1b) Application Example type to similar to this Thread.
To keep a bit in context. A similar practical example that along the way will again show the neat trick demonstrated y the pgc code to get easily all the required indices. The example will be reduced significantly in size which is always better to do in a Forum Threads anyway to solve a real life problem that may be much bigger, but only by duplicating things such as rows and columns : For demonstration purposes a reduced row and column size makes the thread mot readable, and usually a code will be easily adaptable to a larger amount of data.. So let’s say we want to chop out the first 4 column values in the second two rows so that our Output Array takes a form such as

___arrOut() = vB1__vB2__vB3__vB4__vC1__vC2__vC3__vC4___( Note we are referring here to the values in the given Cells, not the Range Objects

The code line required would be of this form

arrOut()=Application.Index(ws.Cells, Array(2, 2, 2, 2, 3, 3, 3, 3), Array(1, 2, 3, 4, 1, 2, 3, 4) )

following along the discussions of Post #11, we can get these required indices easier than just tying them all in: Often for convenience typical Worksheet Formulas are used which in VBA Code we can do by use of the VBA Evaluate Method, which in its simplest form and definition does just that, that is to say evaluates or “does” what Excel does after hitting Enter in a cell with some mathematical expression in it.

Clms()
Here starting with
___Evaluate("=column(A:H)") returning 1, 2, 3, 4, 5, 6 ,7, 8
The above and the next few lines hit a bit of a messy subject, that to do with how Excel deals with Formulas that can, cannot, and can be coerced into giving an Array of values rather than a single value. The first formula is fairly well behaved and in VBA the above line does indeed return a data Field ( of Variant types as this is the returned type of Evaluate ) containing the long Numbers as above.
Hence this code lines gives us no problem and “creates” our column indicia Array
Dim clms() As Variant:Let clms() = Evaluate("=column(A:H)"). In a spreadsheet we would do a form of the ___Crappy Sh_tty Expression Stuff , whereby we ___highlight a Row of 8 cells,___ Hit F2,__ paste the Formula in the Formula bar,__ and finally the famous ..Hit __Ctrl + Shift + Enter. This basically tells Excel we have Arrays in our formula, and ( for this second Type of C S E , distinguished by selecting a number of cells rather than just one in the first tyoe of C S E ) it also assigns where we want to paste out the final evaluated Array values

1​
2​
3​
4​
5​
6​
7​
8​

=COLUMN(A:H)​
=COLUMN(A:H)​
=COLUMN(A:H)​
=COLUMN(A:H)​
=COLUMN(A:H)​
=COLUMN(A:H)​
=COLUMN(A:H)​
=COLUMN(A:H)​

_ we need to do a bit of Maths to get the actual indicia we require, such as that given by pgc
___ = mod(column(A:H),4) = 1, 2, 3, 0, 1, 2, 3, 0 __mod is “bit left over” after taking away as many 4’s as ( if ) possible
___ = mod(column(A:H)-1,4) = 0, 1, 2, 3, 0, 1, 2, 3
___ = mod(column(A:H)-1,4)+1 = 1, 2, 3, 4, 1, 2, 3, 4

The ___Crappy __Sh_tty Expression stuff gives us these values in the Spreadsheet as before
1​
2​
3​
4​
1​
2​
3​
4​

= MOD(COLUMN(A:H)-1,4)+1​
= MOD(COLUMN(A:H)-1,4)+1​
= MOD(COLUMN(A:H)-1,4)+1​
= MOD(COLUMN(A:H)-1,4)+1​
= MOD(COLUMN(A:H)-1,4)+1​
= MOD(COLUMN(A:H)-1,4)+1​
= MOD(COLUMN(A:H)-1,4)+1​
= MOD(COLUMN(A:H)-1,4)+1​

But... At this point we hit a bit of a problem with VBA here. We can demo this by setting a Variant variable equal to the Evaluated Formula instead of the Array variable thus
Dim vTemp As Variant: Let vTemp = Evaluate("=mod(column(A:H),4)") = 1
This returns a long number. It is the same as one gets when one just write the formula in one cell or writes the formula in all cells without doing all the ___Crappy Sh_tty Expression stuff. No one person seems privy to exactly what the problem is and why these things sometimes do work and sometimes not. But to cut a long story and a couple of long Threads short
Multiple Columns Into Single Column Using Data Text To Column
http://www.mrexcel.com/forum/excel-...ons-evaluate-range-vlookup-2.html#post3946289
One can co erce VBA into giving out, that is to say returning via the Evaluate method by typically including an extra part at the start of the formula. An
____=If(Column(A:__), _____mod(column(A:H),4) ____ )
Is a nice one that could allow some additional manipulation of the columns, but there are infinite possibilities... just a few shown here:

Rich (BB code):
Sub clms()
Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("IndexWithArraysWTF") '--Change to Suit Your Sht Preferrence

Dim clms() As Variant: Let clms() = Evaluate("=column(A:H)") ' Returns 1, 2, 3, 4, 5, 6, 7, 8
Dim vTemp As Variant: Let vTemp = Evaluate("=mod(column(A:H),4)") 'Retuns Long Number 1
Let clms() = Evaluate("=If(column(A:H),mod(column(A:H),4))") 'Returns  1, 2, 3, 0, 1, 2, 3, 0
Let clms() = Evaluate("=If(column(A:G),mod(column(A:H),4))") 'Returns  1, 2, 3, 0, 1, 2, 3, error
Let clms() = Evaluate("=If(column(),mod(column(A:H),4))")  'Returns 1, 2, 3, 0, 1, 2, 3, 0
Let clms() = Evaluate("=If(row(),mod(column(A:H)-1,4))") 'Returns 0, 1, 2, 3, 0, 1, 2, 3
Let clms() = Evaluate("=Index((mod(column(A:H)-1,4)+1),)") 'Returns  1, 2, 3, 4,  1, 2, 3, 4

End Sub

_....................................................

rws()
For our rws() some similar maths is needed as well as a coercing bit. The following takes us through some typical steps, we use the Integer ( Int ) here, again a suggested mehtod form pgc Posts #9 and Posts #11

Rich (BB code):
Sub rws()
Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("IndexWithArraysWTF") '--Change to Suit Your Sht Preferrence
Dim rws() As Variant: Let rws() = Evaluate("=column(A:H)") ' Returns 1, 2, 3, 4, 5, 6, 7, 8
Let rws() = Evaluate("=Index((int(column(A:H)/4)),)") 'Returns 0, 0, 0, 1, 1, 1, 1, 2
Let rws() = Evaluate("=Index((int((column(A:H)-1)/4)),)") 'Returns 0, 0, 0, 0, 1, 1, 1, 1
Let rws() = Evaluate("=Index(((int((column(A:H)-1)/4))+1),)") 'returns 1, 1, 1, 1, 2, 2, 2, 2
End Sub

_ ............................................................................................


_ 2 ) Advanced form
The OP actually required a "vertical Output". This was achieved through various code solutions using a simple Transpose of the Array discussed in _ 1) . But here we are discussing the "neat one liner" solution, and require some more in depth discussions in how intimately and internally Excel is working..
It would appear that when VBA ( or Excel in general ) does operations requiring tying up of positions of stuff based on co ordinates, that there is some form of default "intersection searching" going on.
http://www.mrexcel.com/forum/excel-...ket%94-you-have-input-array.html?#post4049222
and in parallel we can influence this a bit sometimes by various Methods.

Consider the last experiment but with a reduced number of “column” indicia, specifically the 1, 2, 3, 4 obtained for example with
Pooclms() = Evaluate("=column(A:D)")’ Returns 1, 2, 3, 4
Consider further reducing the
Poorws() = Evaluate("=column(B:C)")’ Returns 2, 3
this returns us __vB2____vC2____#NV____#NV
Currently both rws() and clms() are 1 Dimensional Arrays ( Experiments show that with 2 Dimensional 1 “row” Arrays the results are identical )

My suggestion would be that Excel “shoots out” “straight” lines:___ “down” from the first argument ( rws() ) and ;____ “across” from the second argument ( clms() ). An aside observation that may give some support to this idea. It was found here
Trouble writing huge array into worksheet range [SOLVED]
that strangely assigning the Elements of a 1 D Array to a “vertical” Spreadsheet Range resulted in the entire ( “1 column ” ) Range to be filled with the first value in that 1 D Array. ( For the case of a similar experiment with a 2 D 1 “column” Array or a 2 D 1 “row” Array , the results were more as expected. That is to say trying to assign in the wrong “orientation” results in just getting the Top Left corner filled. ) This could be telling us that a 1 D Array has a particular meaning inside VBA or maybe the computer itself, and only under certain conditions is “taken” as a Pseudo “Horizontal” Array. Possibly somehow supports my idea that Excel “shoots” out “lines” from the Array Elements of 1 D Arrays. ( or 2 D Arrays being lookes at as 1 D arrays## )
At this point it should also be noted that the following “Tricks” do not work for the Application.WorksheetFunction.Index
Trouble writing huge array into worksheet range [SOLVED]
This could suggests we are “messing about” in the following with some complex ways in which Excel or the computer itself stores Arrays within Function such as .Match and .Index. So it is a co-incidence that it works. ( Note however point !!! )It does not seem to be supported by documentation, and maybe no one really knows what is going on, which worries me a bit in its usage..
Application.Index with Look Up Rows and Columns Arguments as VBA Arrays



But Anyways:

The intercepts of these “Lines” return Element positions. Where these “Elements” are held internally is a complex system based on offset positions to some reference point. The Index Function has a set of code lines to interpret these based on seeing “pseudo” Horizontal given arguments.
_ 2 a) Transpose rws()
Consider only the column indices 1, 2, 3, 4 and let us “mess about” with the “row” indices such that we use a similar Worksheet function , but that for the Spreadsheet Row
_____ rws() = Evaluate("=row(2:3)") 'Returns 2 D 1 "column" Array , values 2 / 3
__.. which returns a 2 Dimensional 1 “column” Array. This is effectively the transpose of what the .Index was “expecting”. I would suggest it is “shooting” out lines similar to before for the “columns” ( just 4 of them instead of 8 of them this time. ). But i would suggest for “row” indicia 2 now a line “shoots” out in a “line” with a transposed orientation and hence intercepts now all the “column” indices. For “row” indicia 3 a similar “line” “shoots” out along the same “lines” or “track” that indicia 2 did – It sort of has the same position in what the index is “trying” to “look” at ## as a 1 D array argument. But by virtue of the transpose caused by rws() being a 2 D 1 “column” Array it starts “set forward” along this track. So it has a different relative offset position in the Excel or Computer memory
Without knowing exactly what is going on deep down internally one can only guess, but a good guess might be that the index working with its normal Interpretation rules of the various offsets and intercepted points may return a 1 D Array of all columns for “row” indicia 2, __ and do the same for “row” indicia 3 except “set” forward by an offset which will be interpreted as a “Row” offset. So a 2 D 4 “column” To 2 “row” would be returned such as this:
vA2​
vB2​
vC2​
vD2​
vA3​
vB3​
vC3​
vD3​
Sheet: IndexWithArraysWTF
___for arrOut() in such a code:
Rich (BB code):
Sub AppIndexRT23C1234()
Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("IndexWithArraysWTF")
Dim rws() As Variant: Let rws() = Evaluate("=row(2:3)") 'Returns 2 D 1 "column" Array , values  2 / 3
Dim clms() As Variant: Let clms() = Evaluate("=column(A:D)") ' Returns 1, 2, 3, 4

Dim arrOut() As Variant
Let arrOut() = Application.Index(ws.Cells, rws(), clms())
End Sub

Indeed it does!. I confess i already knew the answer. But I did since about 6 Months and this is the first time I have found any suggested Theory from anyone!
Application.Index with Look Up Rows and Columns Arguments as VBA Arrays

_ ...............................

2b) Transpose rws() and clms()
I think it is a reasonable argument now to say that if I now go on to Transpose the clms() as well, I will be back to the situation of the “lines” being at right angles to each other and so only returning single intercept positions. But the offset caused by the two “identical” internally held “offsets” within the VBA or computer memory are interpreted by the .Index to give a Transposed final output. Such that this code:
'
Rich (BB code):
Sub AppIndexRT23CT1234()
Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("IndexWithArraysWTF")
Dim rws() As Variant: Let rws() = Evaluate("=row(2:3)") 'Returns 2 D 1 "column" Array , values  2 / 3
Dim clms() As Variant: Let clms() = Evaluate("=row(1:4)") 'Returns 2 D 1 "column" Array , values  1 / 2 / 3 / 4

Dim arrOut() As Variant
Let arrOut() = Application.Index(ws.Cells, rws(), clms())
End Sub
___will return this for arrOut()
vA2​
vB3​
error​
error​

_ this basic form meets the OP requirement when appropriate “row” and “column” indices are chosen.


_ ........

_ 2 c) Transpose only clms()

Just for completeness, we can explain the above scenario in words: “The column” indices “shoot” out along the same track but each with at a different “offset” start point. Each “column” “line” intercepts the both 2 “row” indices. Each “column” Line is therefore interpreted as an Array of 2 elements of all “row” elements for that “column” by the .Index function. But by virtue of the different “start” “offset” held within the VBA or Computer memory the .Index interprets that these Arrays are “stacked” on top of eachother, rather than tacked “at the end” to give a single “row”
So this code:
Rich (BB code):
'
Sub AppIndexR23CT1234()
Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("IndexWithArraysWTF")
Dim rws() As Variant: Let rws() = Evaluate("column(B:C)") 'Returns 1 D Array   2, 3
Dim clms() As Variant: Let clms() = Evaluate("=row(1:4)") 'Returns 2 D 1 "column" Array , values  1 / 2 / 3 / 4

Dim arrOut() As Variant
Let arrOut() = Application.Index(ws.Cells, rws(), clms())
End Sub

___returns this for arrOut()

vA2​
vA3​
vB2​
vB3​
vC2​
vC3​
vD2​
vD3​

_ ................................................................

Some last thoughts:

_ ( i ) I cannot quite follow the logic that results in codes of the form below with the various variations or rws() argument return the following
vA2​
vB2​
vC2​
vD2​
_ ... Maybe the Application.Index “rows” and “column” argument are as Variant declared ( could not google this for Application.Index :) Application.WorksheetFunction.Index is defined as Long for “row” and variant for “column” arguments – another thing preventing it from “working” with the “tricks” !!! ) ). Maybe then this somehow “shoots” differently such as to intercept all columns.
Rich (BB code):
Sub AppIndexR2_C1234()
Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("IndexWithArraysWTF")
Dim rws() As Variant: Let rws() = Array("2") 'Returns 1 D 1 Variant Element Array holding  String value of 2
Let rws() = Array(2) 'Returns 1 D 1 Variant Element Array holding  Long value of 2
Dim rws2(1 To 1, 1 To 1): Let rws2(1, 1) = "2"
Dim clms() As Variant: Let clms() = Evaluate("=column(A:D)") 'Returns 1 D 1 "row" Array   1, 2, 3, 4

Dim arrOut() As Variant
Let arrOut() = Application.Index(ws.Cells, rws(), clms())
Let arrOut() = Application.Index(ws.Cells, 2, clms())
Let arrOut() = Application.Index(ws.Cells, rws2(), clms())
End Sub

_ ( ii ) The last example can also be achieved with the classic “Slicing” Technique....
https://usefulgyaan.wordpress.com/2...cing-an-array-without-loop-application-index/
____...with such a code:
'
Rich (BB code):
Sub AppIndexSptR2_C1234()
Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("IndexWithArraysWTF")
Dim arrIn() As Variant: Let arrIn() = ws.Range("A1:D4").Value 'Restrict Input Array for split demo

Dim arrOut() As Variant
Let arrOut() = Application.Index(arrIn(), 2, 0)

End Sub

______... I suspect the argument __0__ somehow instruct VBA to take the argument as the full Array of all indicies

_ Hope this is / was a contribution to this Thread, and maybe the subject in general of creating Arrays from other Arrays. Maybe a last couple observation here. As Noted here:
http://www.mrexcel.com/forum/excel-...to-one-array-visual-basic-applications-3.html
Trouble writing huge array into worksheet range [SOLVED] - Page 2

_ A) The use of .Cells as our first argument is not subject to the usually Array Limits. ###
_ B) The cells in a worksheet could be considered an enormous available ( for XL 2007 + ) “Array”

_ These two points return a nice way to do a consolidation of Arrays, even from Different Worksheets or Workbooks.
Firstly all Ranges would be brought into a main sheet with simple ( “pseudo” ) code lines

ArbritraryRangeInMainSheet.Value=EachRange.Value

Then a simple “one liner” based on careful selection of the Numbers in, and orientation of, the rws() and clms() Arrays as discussed in this Thread could be used to obtain a final Array of required form and size and orientation.


Alan
 
Upvote 0
Hi Alan

I see you have really been reflecting on this theme.

I don't think this type of solution is very practical, I would probably not use it unless it's a simple case, but I also think that it is good to understand the syntax of the functions we use, and in that spirit ...

for the sake of being thorough in your reflection I would just have one remark: you should maybe not ignore the 4th parameter of Index(). It allows you to address different areas in a multiarea range and that gives you anoter level of flexibility.

This is a simple example that illustrates it:

You have a multiarea range with 3 equal sized areas 2 rows * 4 columns.

You want to write all the data in a 12 rows * 2 columns range in groups of 2 columns of the areas on top of each other, like

Area 1 columns 1-2
Area 1 columns 3-4
Area 2 columns 1-2
Area 2 columns 3-4
Area 3 columns 1-2
Area 3 columns 3-4

You are transposing the multi-area range to a 2 column range.


For my test I used the range with the areas B2:E3, D6:G7and A11:D12. I defined the name rAreas as the name of this range.

The code I used:

Code:
Sub TransposeMultiAreaRange()

Range("I2:J13") = Application.Index(Range("rAreas"), _
    Evaluate("if(row(1:12)*column(A:B),mod(row(1:12)-1,2)+1)"), _
    Evaluate("if(row(1:12)*column(A:B),2*int(mod(row(1:12)-1,4)/2)+column(a:b))"), _
    Evaluate("if(row(1:12)*column(A:B),int((row(1:12)-1)/4)+1)"))

End Sub

This is just an example, you have now 3 degrees of freedom and you can combine the parameters for many other requests.

This is the data I used.


<table border="1" cellpadding="1" style="background:#FFF; border-collapse:collapse;border-width:2px;border-color:#CCCCCC;font-family:Arial,Arial; font-size:10pt" ><tr><th style="border-width:1px;border-color:#888888;background:#9CF " > </th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >A</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >B</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >C</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >D</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >E</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >F</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >G</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >H</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >I</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >J</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >K</th></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>1</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">Area 1</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>2</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#FFFF00;text-align:left;border-width: 1px;border-color:#888888; ">A1 R1C1</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#FFFF00;text-align:left;border-width: 1px;border-color:#888888; ">A1 R1C2</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#FFFF00;text-align:left;border-width: 1px;border-color:#888888; ">A1 R1C3</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#FFFF00;text-align:left;border-width: 1px;border-color:#888888; ">A1 R1C4</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">A1 R1C1</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">A1 R1C2</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>3</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#FFFF00;text-align:left;border-width: 1px;border-color:#888888; ">A1 R2C1</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#FFFF00;text-align:left;border-width: 1px;border-color:#888888; ">A1 R2C2</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#FFFF00;text-align:left;border-width: 1px;border-color:#888888; ">A1 R2C3</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#FFFF00;text-align:left;border-width: 1px;border-color:#888888; ">A1 R2C4</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">A1 R2C1</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">A1 R2C2</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>4</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">A1 R1C3</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">A1 R1C4</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>5</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">Area 2</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">A1 R2C3</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">A1 R2C4</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>6</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#FFFF00;text-align:left;border-width: 1px;border-color:#888888; ">A2 R1C1</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#FFFF00;text-align:left;border-width: 1px;border-color:#888888; ">A2 R1C2</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#FFFF00;text-align:left;border-width: 1px;border-color:#888888; ">A2 R1C3</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#FFFF00;text-align:left;border-width: 1px;border-color:#888888; ">A2 R1C4</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">A2 R1C1</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">A2 R1C2</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>7</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#FFFF00;text-align:left;border-width: 1px;border-color:#888888; ">A2 R2C1</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#FFFF00;text-align:left;border-width: 1px;border-color:#888888; ">A2 R2C2</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#FFFF00;text-align:left;border-width: 1px;border-color:#888888; ">A2 R2C3</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#FFFF00;text-align:left;border-width: 1px;border-color:#888888; ">A2 R2C4</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">A2 R2C1</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">A2 R2C2</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>8</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">A2 R1C3</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">A2 R1C4</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>9</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">A2 R2C3</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">A2 R2C4</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>10</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">Area 3</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">A3 R1C1</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">A3 R1C2</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>11</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#FFFF00;text-align:left;border-width: 1px;border-color:#888888; ">A3 R1C1</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#FFFF00;text-align:left;border-width: 1px;border-color:#888888; ">A3 R1C2</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#FFFF00;text-align:left;border-width: 1px;border-color:#888888; ">A3 R1C3</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#FFFF00;text-align:left;border-width: 1px;border-color:#888888; ">A3 R1C4</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">A3 R2C1</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">A3 R2C2</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>12</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#FFFF00;text-align:left;border-width: 1px;border-color:#888888; ">A3 R2C1</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#FFFF00;text-align:left;border-width: 1px;border-color:#888888; ">A3 R2C2</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#FFFF00;text-align:left;border-width: 1px;border-color:#888888; ">A3 R2C3</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#FFFF00;text-align:left;border-width: 1px;border-color:#888888; ">A3 R2C4</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">A3 R1C3</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">A3 R1C4</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>13</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">A3 R2C3</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">A3 R2C4</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>14</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td colspan=12 style="background:#9CF; padding-left:1em" > [Book1]Sheet1</td></tr></table>


You could, of course, also use the formula directly in the worksheet.
 
Upvote 0
I'm very glad to see this theme evolving :) and you guys rippin at it. Unfortunately, my coding skills are VERY rudimentary (basically self taught in web technologies and traversing into VB) so I can not really follow along that well. It's also a bit of a challenge learning while tackling a real case here at work, I certainly do not want to run a proceedure on a large data set and not feel confident if it ehm, "did well" :D

I'll try and multi-quote some of the original responses.

/in the end I went with the first solution, as that is the one that I completely understand/
 
Upvote 0
@ astrbac,
Thanks for the feedback
Glad we helped,
and hope we are not confusing You too much, by taking it further**. And i agree , as said before, the first codes are probably the best for you at this stage if your data is not too much and speed is not too important ( I would take out the
Application.ScreenUpdating = False
__bit as well – you can see then a bit better what is going on, and it then avoids the danger of the screen staying “dead” should something go wrong and the program crashes . ( you can leave the
Application.ScreenUpdating = True
__bit in or take it out. - Makes no difference, - Does no harm if the code tries to turn the screen updating on when it already is turned on )

What___Application.ScreenUpdating = False___does is just disable Excel from updating the screen every time anything is done: You see if you change any cell content for example, then normally Excel will go through and update the whole Worksheet!! So often it is disabled at the start of the code then re abled at the end so it just does that updating once. But it is not necessary for small data..


__I sympathise that it is very difficult to, learn at the same time as being under pressure to get a real job done. That is exactly the situation I am in! , but i learn time and time again that it is always saves time in the long run to understand as much as you have time to. If you want anything explained better, let us know-.

__I find some of the Multi solution Threads are the best places to learn** and many of us are here to both help and learn, - especially when people like pgc come in – what he knows is often not to be found in any books.... I spent 6 Months trying to understand the ”................ use of Application.Index with Look Up Rows and Columns Arguments as VBA Arrays. …..“ stuff , got no where , and his input here has helped me get on “Track” a bit.. I am going to tear myself away now, make a coffee, and go through in detail his last input.....

Good luck with your project
Alan

Wonk Wonk
 
Upvote 0
DocAElstein

No problem, I know how it feels when you go through the effort of literally taking someone by the hand and leading them only to have them not even say thanks, let alone write some decent feedback.

I am ?=$%&& frustrated today :) because I thought I'd be much quicker to pick up this stuff. My main challenges now are bits and pieces; i understand the grand stuff/concepts like:

_ object hierarchy
_ properties, methods, events
_ procedures, functions, scope and lifetime
_ modules, organization of an app
_ classes, construction, destruction...

...

For example, I fully understand what is a range, how to select it, what is an array, how to access or modify its elements, BUT... a situation comes (like now), when I have a completely different yet similar problem and I can't solve it:

- put two non adjacent columns (say C1:C30 and E1:E30) into an array
- write them down in a single column range, one under the other (same column)

OR

- copying range of one size
- pasting it into other ranges, varying sizes and with loops

I mostly need to manipulate data which is of course, as always, interspersed across different sheets in documents, whereas I want to have it written properly, line by line. That way I can create pivot tables, calculate stuff, create charts... I am frustrated as I seem to be spending most of my time creating "skeletons" like:

COLUMN A
20 rows Austria
20 rows Belgium
20 rows Czech, ...

Then in the adjacent column B

10 Austria's get "string 1" beside them
10 other Austrias get "string2"
10 Belgiums get "string 1"
10 Belgiums "string2"

...


Maybe my mind is just not made up for this :D and i should stick to marketing/sales. (or, if I had someone beside me who knows this stuff to teach me I could learn. Not an option for now unfortunately).
 
Upvote 0

Forum statistics

Threads
1,212,938
Messages
6,110,789
Members
448,297
Latest member
carmadgar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top