Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Array Manipulation with VBA coding
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Array Manipulation with VBA coding
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 -- Regards PY & Associates "Joe" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Array Manipulation with VBA coding
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" wrote: 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Array Manipulation with VBA coding
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 ? "JMB" wrote: 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" wrote: 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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Array Manipulation with VBA coding
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) "Joe" wrote in message ... 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 ? "JMB" wrote: 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" wrote: 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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Array Manipulation with VBA coding
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. "Joe" wrote: 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 ? "JMB" wrote: 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" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
array manipulation | Excel Worksheet Functions | |||
Text manipulation | Excel Worksheet Functions | |||
Array Manipulation | Excel Worksheet Functions | |||
Array Manipulation | Excel Worksheet Functions | |||
Array Manipulation | Excel Worksheet Functions |