Results 1 to 7 of 7

Thread: How do I insert non-adjacent columns into an array?
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    May 2017
    Location
    Alberta, Canada
    Posts
    176
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

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

    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.
    Code:
    Option Base 1
    
      Sub LoadArray()
          Dim Ary as Variant
          Dim NumRows as Long
      Dim ws1 As Worksheet: Set ws1 = ThisWorkbook.Sheets("Sheet1")
      NumRows =  Range("B"&Rows.Count).End(xlUp).Row
      Ary = Range("B4:B" & NumRows)        ' This loads column 'B' into ARY
      ' Here is where I start picking your brains for some brilliant lines of code.
      ' What do I do now to load columns 'D, N, Q, Y'?
      End Sub
    THANK YOU in advance for any help or suggestions you may be able to offer.
    TotallyConfused
    - If you have some code, let us see it because that is always a big help. If it only partially works, where does it stop and what is the error message? Please enclose any code within the code blocks. If you aren’t sure how to do this, highlight the code, then click on the ‘#’ button along the top of your screen.

    - forum use guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

  2. #2
    Board Regular steve the fish's Avatar
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,649
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    3 Thread(s)

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

    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:

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

  3. #3
    Board Regular
    Join Date
    May 2017
    Location
    Alberta, Canada
    Posts
    176
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

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

    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
    Last edited by TotallyConfused; Feb 23rd, 2019 at 04:44 PM.
    - If you have some code, let us see it because that is always a big help. If it only partially works, where does it stop and what is the error message? Please enclose any code within the code blocks. If you aren’t sure how to do this, highlight the code, then click on the ‘#’ button along the top of your screen.

    - forum use guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

  4. #4
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,244
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

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

    Quote Originally Posted by TotallyConfused View Post
    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...
    Code:
    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, ",")) ' 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. Sheets("Sheet2").Range("A1").Resize(UBound(Arr, 1), UBound(Arr, 2)) = Arr End Sub
    Last edited by Rick Rothstein; Feb 23rd, 2019 at 05:34 PM.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  5. #5
    Board Regular
    Join Date
    May 2017
    Location
    Alberta, Canada
    Posts
    176
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

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

    @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:
    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)
    Last edited by TotallyConfused; Mar 1st, 2019 at 05:14 AM.
    - If you have some code, let us see it because that is always a big help. If it only partially works, where does it stop and what is the error message? Please enclose any code within the code blocks. If you aren’t sure how to do this, highlight the code, then click on the ‘#’ button along the top of your screen.

    - forum use guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

  6. #6
    Board Regular
    Join Date
    May 2017
    Location
    Alberta, Canada
    Posts
    176
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

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

    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
    Last edited by TotallyConfused; Mar 4th, 2019 at 06:05 AM.
    - If you have some code, let us see it because that is always a big help. If it only partially works, where does it stop and what is the error message? Please enclose any code within the code blocks. If you aren’t sure how to do this, highlight the code, then click on the ‘#’ button along the top of your screen.

    - forum use guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

  7. #7
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,190
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

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

    How about
    Code:
        arr = Application.Index(.Range("A4:Q" & NumRows).Value, Evaluate("Row(1:" & NumRows & ")"), Split(LoadCols, ","))
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •