Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using array to select rows to copy
I have a two dimensional array dim'd as variant. The elements in the first dimension are strings and the elements in the 2nd dimension are integer row numbers cooresponding to the location of the 1st dimension elements. I want to copy blocks of rows that are defined as starting at the first (2nd dimension) element and ending 1 row before the 2nd (2nd dimension) element etc.
That probably isn't very clear. dim myarray(18,2) 'code to fill the array 'beginning of array may look like this - [("001" , 3), ("016", 15), ("022", 34).....] i want to copy rows 3 to 14 onto the worksheet named "001", then copy rows 15 to 33 to the sheet named "016". The macro recorder game me this snippet of code: Rows("3:14").Select Selection.copy 'etc I need to replace the "3:14" above with a reference to the (variable) elements in my array. I hope this makes sense TIA, Marcotte |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using array to select rows to copy
You could try it this way:
Dim MyArray(18,2) as Variant Dim StartRow as Long, EndRow as Long, i as Integer For i = 1 to 17 StartRow = MyArray(i,2) EndRow = MyArray(i+1,2) - 1 Next i -----Original Message----- I have a two dimensional array dim'd as variant. The elements in the first dimension are strings and the elements in the 2nd dimension are integer row numbers cooresponding to the location of the 1st dimension elements. I want to copy blocks of rows that are defined as starting at the first (2nd dimension) element and ending 1 row before the 2nd (2nd dimension) element etc. That probably isn't very clear. dim myarray(18,2) 'code to fill the array 'beginning of array may look like this - [("001" , 3), ("016", 15), ("022", 34).....] i want to copy rows 3 to 14 onto the worksheet named "001", then copy rows 15 to 33 to the sheet named "016". The macro recorder game me this snippet of code: Rows("3:14").Select Selection.copy 'etc I need to replace the "3:14" above with a reference to the (variable) elements in my array. I hope this makes sense TIA, Marcotte . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using array to select rows to copy
You could try it this way:
Dim MyArray(18,2) as Variant Dim StartRow as Long, EndRow as Long, i as Integer For i = 1 to 17 StartRow = MyArray(i,2) EndRow = MyArray(i+1,2) - 1 Next i -----Original Message----- I have a two dimensional array dim'd as variant. The elements in the first dimension are strings and the elements in the 2nd dimension are integer row numbers cooresponding to the location of the 1st dimension elements. I want to copy blocks of rows that are defined as starting at the first (2nd dimension) element and ending 1 row before the 2nd (2nd dimension) element etc. That probably isn't very clear. dim myarray(18,2) 'code to fill the array 'beginning of array may look like this - [("001" , 3), ("016", 15), ("022", 34).....] i want to copy rows 3 to 14 onto the worksheet named "001", then copy rows 15 to 33 to the sheet named "016". The macro recorder game me this snippet of code: Rows("3:14").Select Selection.copy 'etc I need to replace the "3:14" above with a reference to the (variable) elements in my array. I hope this makes sense TIA, Marcotte . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using array to select rows to copy
Oops - accidentally hit send before done!
Anyway, before the Next i statement now you just need to copy the rows: Rows(StartRow & ":" & EndRow).Select Selection.copy .... etc. Hope this does what you want! -----Original Message----- I have a two dimensional array dim'd as variant. The elements in the first dimension are strings and the elements in the 2nd dimension are integer row numbers cooresponding to the location of the 1st dimension elements. I want to copy blocks of rows that are defined as starting at the first (2nd dimension) element and ending 1 row before the 2nd (2nd dimension) element etc. That probably isn't very clear. dim myarray(18,2) 'code to fill the array 'beginning of array may look like this - [("001" , 3), ("016", 15), ("022", 34).....] i want to copy rows 3 to 14 onto the worksheet named "001", then copy rows 15 to 33 to the sheet named "016". The macro recorder game me this snippet of code: Rows("3:14").Select Selection.copy 'etc I need to replace the "3:14" above with a reference to the (variable) elements in my array. I hope this makes sense TIA, Marcotte . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Select range then copy rows macro | Excel Discussion (Misc queries) | |||
macro to select and copy rows only containing data | Excel Discussion (Misc queries) | |||
Select alternate rows to copy | Excel Discussion (Misc queries) | |||
Select Range every 20 rows copy & paste special | Excel Programming | |||
Automatically copy select rows to different page? | Excel Programming |