How do I insert non-adjacent columns into an array?

[TotallyConfused]

Hello all you MrExcel experts

I have a sheet with 26 columns and approximately 250,000 rows of data. The number of columns will stay the same, however, the number of rows will increase. I will be comparing data in columns 'B, D, N, Q', and depending of the results of that comparison, some data may or may not be placed in column 'Y' (which will be the fifth element of the array). None of the other columns will be accessed. I think I can do the comparison and the writing of data to that fifth element. To speed up the processing time, I'd like to load these five columns into an array, do the processing there, then write the data back to Excel. I could create an array that holds the whole spreadsheet, but considering the limited number of columns that are involved, that seems like an inefficient way to code, as well as possible memory limitations due to the large amount of data.

I know I could set up a loop where I'd load individual cells, but with 5 columns, each with a quarter million or more rows, that is a lot of loops. Another option I've considered, is to create a dummy sheet, copy the above mentioned columns to it, load it into the array, do the processing and then delete that sheet. Surely there must be a better way. I have two questions.

1 ) I tried splitting the loading process into five parts and load one column at a time. Column 'B' loads fine as the first element of the array (as shown in my sample code). From then on, I'd either overwrite the first element or get various error messages depending on what I tried. I've tried using 'ReDim' and 'ReDim Preserve', but since I'm not sure how to handle those statements, I met with failure. Is it possible to load only these five non-adjacent columns into an array, and if so, how?

2 ) During the processing, only the fifth element of the array, (which is column 'Y') will be changed. This is the only one that needs to be written back to Excel, and it will be placed in its original position, starting at 'Y4'. How do I write this one array element back to column 'Y' of the spreadsheet?

I've tried various versions of the following macro, but this is what I'm looking at now.

[COLOR=black][FONT=Calibri]Option Base 1[/FONT][/COLOR]

  [COLOR=black][FONT=Calibri]Sub LoadArray()[/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]    Dim Ary as Variant[/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]    Dim NumRows as Long[/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]Dim ws1 As Worksheet: Set ws1 = ThisWorkbook.Sheets("Sheet1")[/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]NumRows = [/FONT][/COLOR][COLOR=black][FONT=Calibri] Range("B"&Rows.Count).End(xlUp).Row[/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]Ary = Range("B4:B" & NumRows)        ' This loads column 'B' into ARY[/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]' Here is where I start picking your brains for some brilliant lines of code.[/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]' What do I do now to load columns 'D, N, Q, Y'?[/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]End Sub[/FONT][/COLOR]

THANK YOU in advance for any help or suggestions you may be able to offer.
TotallyConfused

 

[steve the fish]

Why not just put B:Y into an array? Doesnt really matter if you dont use some of it. Then you can put it back on the worksheet like this:

Ary = Range("B4:Y" & NumRows)
'do your processing
Range("Y4:Y" & NumRows) = Application.Index(Ary, 0, 24)

 

[TotallyConfused]

THANK YOU Steve.

That is probably the way I'll go. At least that way, everything will already be loaded if at some time any other columns need to be accessed. Just out of curiosity, I'm wondering, is there some way to load individual columns into an array? I did a little, though I'll admit not that much, research on the internet, but the only methods I found involved loading one cell at a time into the array.

Thanks again for your advice.

TotallyConfused

 

[Rick Rothstein]

Just out of curiosity, I'm wondering, is there some way to load individual columns into an array?

Put some data on Sheet1 in the cells of range A:H from Row 1 to as many rows as you like, then run this macro...

[table="width: 500"]
[tr]
	[td]Sub TestLoadingNonAdjacentColumnsIntoAnArray()
  Dim LastRow As Long, Arr As Variant, Cols As String
  Cols = "2,4,5,7"
  LastRow = Cells.Find("*", , xlValues, , xlRows, xlPrevious, , , False).Row
  Arr = Application.Index(Cells, Evaluate("ROW(1:" & LastRow & ")"), Split(Cols, ","))
  
[B][COLOR="#008000"]  ' Arr now holds 4 columns of data (Columns B,D,E and G).
  ' To see this, we'll put the array of values on Sheet2.
[/COLOR][/B]  Sheets("Sheet2").Range("A1").Resize(UBound(Arr, 1), UBound(Arr, 2)) = Arr
End Sub[/td]
[/tr]
[/table]

 

[TotallyConfused]

@Rick Rothstein
In posting #4 , you showed me how to load non-adjacent columns into an array and then write them back to Excel. It worked like a charm. I can't THANK YOU enough for your help. You are an amazing programmer, so with your permission, I'd like to pick your brain for a little more information.

Using your example, I need to write ONLY Arr column #2 (which would be Sheet1 column D) into Sheet2 column 'F'. I've figured out how to do that with the following bit of code:

Dim NumRows As Long
Dim ws1 As Worksheet: Set ws1 = ThisWorkbook.Sheets("Sheet1")
With ws1
    NumRows = .Cells(.Rows.Count, "B").End(xlUp).Row
    LoadCols = "2,4,5,7" 
    Arr = Application.Index(Cells, Evaluate("Row(1:" & NumRows & ")"), Split(LoadCols, ","))
End With
   ' Now paste Arr column 2 into Sheet2, column 'F' starting at row 2
Sheets.Sheet2.Range("F2:F" & NumRows) = Application.Index(Arr, 0, 2)

My problem is that column 'D' from Sheet1 has 3 rows of headings (data starting in row 4) so this is all loaded into the array. Sheet2 has only 1 row of headings, so the data now needs to start in Row 2. I need some way to get rid of the three rows of headings.

One solution I've thought of would be to copy Sheet1, columns '2,4,5,7' into a helper sheet, delete the first three rows (all the headings) then load what remains into Arr. That sheet could then be deleted. This might work, but I don't know if it is the best solution.
Now my question:

1) How do I load ONLY the actual data from Arr column 2 into Sheet2 column 'F' starting at Row 2?

Now that I'm thinking of arrays, columns and sheets, I have a couple of supplemental questions.

2) Again using the above example, is it possible to load Sheet1, columns 2 & 4 AND Sheet2, columns 5 & 7 into one array? This array would then have 4 columns. If this can be done, it would be a big help with my current project and would save several lines of code.

3) You have shown a quick and efficient way to copy multiple columns into an array, so is there a similar way to copy multiple columns from one sheet to another?

4) I noticed you used this line to determine the number of rows.

LastRow = Cells.Find("*", , xlValues, , xlRows, xlPrevious, , , False).Row

That is a way I don't think I've seen used before. There are several parameters, so I'll have to do some research on it to see how it works. Is it more efficient than:

NumRows = .Cells(.Rows.Count, "B").End(xlUp).Row

Once again, THANK YOU for any help or advice you may be able to offer. It is much appreciated.

TotallyConfused (one day with help from all the experts here, I hope to be only PartiallyConfused)

 

[TotallyConfused]

Hello anyone.

Is there anyone that could suggest any solutions to the above questions? I sure would appreciate any help you may be able to offer. Thank you in advance.

TotallyConfused

 

[Fluff]

How about

    arr = Application.Index(.Range("A4:Q" & NumRows).Value, Evaluate("Row(1:" & NumRows & ")"), Split(LoadCols, ","))