Populating 2D array with non-contiguous ranges

[jkordzi]

Hi, This is my first post. I've used Excel for a long time but am new to VBA. I have a sheet that has about a million rows and 30 columns of data that is already sorted in a nested fashion based on descending values in three of the the columns. I currently have a macro that goes through each row, selects specific cells from non-contiguous columns (these columns never change), assigns them to a range and then pastes them to another sheet in another workbook. The process continues until the top 25 values based on the third sort criteria are passed to the table. Then the macro skips the remaining rows (because the values in the third sort criteria are out of the top 25) until it gets a new value for the second sort criteria, whereupon it starts a new table for another top 25. The macro works but is very slow because each time it reads a range from one sheet, it then activates the other sheet and pastes a row. So I'd instead of going back and forth between sheets and copying and pasting each row, I'd like to read large blocks from the first sheet into a 2D array first, and then activate the second sheet and paste it all at once. My problem is that I can't figure out how to pass the non-contiguous range to the 2D array. I've seen posts on how to pass an entire row to an array, or how to pass an entire spreadsheet into an array, but not a range that is made up of specific cells from a row. The range will always have the same columns.

So I was hoping someone would pass along some sample code that reads a non-contiguous range into a variable length 2D array using a For Loop. The range is as follows:

Workbooks("test.xlsm").Worksheets("Data").Range("A" & i & ", D" & i & ", F" & i & ", G" & i & ", K" & i & ", L" & i & ", N" & i & ", P" & i & ", Q" & i & ", Y" & i & ", AC" & i).Copy

The loop has different If-Then-Else criteria but I think I can handle that. Thanks.

 

[Rick Rothstein]

You might find this hard to believe, but you do not need an array nor a loop to place the values in 25 contiguous rows containing 11 non-contiguous columns onto another worksheet. Here is an example that pulls the data from the sheet named Data in the non-contiguous columns you indicated you wanted for rows 2 through 26... the code will place that data onto Sheet2 (change this to an empty sheet in your workbook) starting at cell C5 (chosen just to show you the flexibility).

[table="width: 500"]
[tr]
	[td]Sub ArrayForNonContigColumnsBy25Rows()
  Dim StartRow As Long, EndRow As Long
  StartRow = 2
  EndRow = StartRow + 25
  Sheets("Sheet2").Range("C5").Resize(25, 11) = Application.Index(Sheets("Data").Cells, Evaluate("ROW(" & StartRow & ":" & EndRow & ")"), Split("1 4 6 7 11 12 14 16 17 25 29"))
End Sub[/td]
[/tr]
[/table]

I'll leave it as an exercise for you to modify the references as needed.

 

[jkordzi]

Thanks Rick. I'm going to have to do some research to understand exactly what that next to last line does. For simplicity, I didn't go into detail about the rest of my problem, but I do have to first ensure that I have at least 25 rows available to make a table, otherwise the table gets made based on what's available, but I think I can come up with a way to first make that test. My bigger concern is that having written the table to the other sheet I would have to pick up where I left off in the original data and continue on with the rest of the million rows. So I'm not sure really helps me speed up the process as much as it would if I could copy say a hundred of these tables into an array and then paste them into Sheet2 all at once?

 

[Rick Rothstein]

Thanks Rick. I'm going to have to do some research to understand exactly what that next to last line does. For simplicity, I didn't go into detail about the rest of my problem, but I do have to first ensure that I have at least 25 rows available to make a table, otherwise the table gets made based on what's available, but I think I can come up with a way to first make that test. My bigger concern is that having written the table to the other sheet I would have to pick up where I left off in the original data and continue on with the rest of the million rows. So I'm not sure really helps me speed up the process as much as it would if I could copy say a hundred of these tables into an array and then paste them into Sheet2 all at once?

If you give us some idea of how the data should look in the sheet you are copying to, we may be able to give you more robust code to implement what you want...

1) Are you wanting to create real Excel Table objects or just ranges of cells that you think of as "tables"?

2) Will the tables have headers?

3) Will there be blank rows between them? Will they simply be placed one under the other or are you planning on arranging them in some kind of two-dimension "array"?

4) Also, how are you choosing with 25 rows get copied over to that other sheet?