Array Manipulation with VBA coding

[Joe]

I have large array which I would like to manipulate by VBA macros.

The array size is A1:Z100 ( 26 columns X 100 rows)

The data in A:column (text) and in B:column (numeric) are related to each
other.
That is paired to each other.
Likewise in C: (text) and in D: (numeric) and so on until Y: Z:

There are however blanks in both corresponding columns at the end of some
rows.

I need to move each pair of columns sequentially starting with C: D:
...........down to Y:Z:
underneath A: and B:

Being brand new to VBA, until this week (thanks goes to JMB for introducing
me to VBA...I have discovered the VBA language manual) I am still struggling
with the synatx.

 

[PY & Associates]

Sub m()
Dim rng As Range
For i = 3 To 26 Step 2
Set rng = Range(Cells(1, i), Cells(1, i + 1).End(xlDown))
rng.Cut Cells(Rows.Count, 1).End(xlUp)(2)
Next i
End Sub

 

[JMB]

Some info on referencing ranges here that may be worth a look. Of course,
there is much else to learn about referencing ranges.
http://cpearson.com/excel/cells.htm

If your data is important - be sure to backup before trying because VBA does
not have a built in undo feature.

Sub test()
Dim i As Long

For i = 1 To 12
Range(Cells(1, i * 2 + 1), Cells(Rows.Count, _
i * 2 + 1).End(xlUp)).Resize(, 2).Cut _
Cells(Rows.Count, 1).End(xlUp)(2, 1)
Next i

End Sub

 

[Joe]

Excellent. Both suggested codes work.
I do not understand the syntax of the .End(xlUp) (2,1)
part
To what does the (2,1) part refer to ? The VBA Language reference End
Property does not seem to mention it.
Likewise the .End(xlUp)(2) in the PY&A code
Is it part of the Cells syntax ?

 

[Bob Phillips]

the second row and first column from that point, i.e one row down. This is
finding the end of the data and then pasting the results at the foot + 1
row.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[JMB]

It is just one other method of referencing ranges. Range references always
depend on the reference point from which you start. To give you some
examples -

Range("A1") refers to cell A1 (of course)

Columns(3).Range("A1") refers to cell C1 (because column C is your beginning
reference point - "A1" means the cell in the upper left corner of whatever
range you are starting with. In this case, you are starting w/Column C).

Range("A1") (1,1) refers to cell A1 (but the (1,1) is unnecessary).
Range("A1") (3, 1) refers to cell A3

Range("A1") (3, 1) (2, 2) refers to cell B4 (because Range("A1") (3, 1) is
cell A3, which is considered (1,1) so (2,2) is one row down and one column
over from cell A3).

Just don't confuse this method of referencing a range w/the Offset method.
Using Offset:

Range("A3").Offset(1, 1) refers to cell B4 while Range("A3") (2, 2) would
refer to cell B4.

Range("B3").Offset(0, 0) still refers to cell B3 while Range("B3") (0, 0)
would refer to cell A2.