none
Combine 3 cells into 1 over 20000 lines in a faster way? RRS feed

  • Question

  • Dear All,

    I am having the problem, that i have an XLSM where i have several tabs (not more then 10). Each of these tabs I have part numbers splietted in 3 cells. for example aaa-bbb-ccc will look like A1=aaa B1=bbb and C1=ccc. 

    Tab contains average 2000 lines.

    What I would like to do in D cell to create aaabbbccc, so for that i run =A1&B1&C1 code and after it, i will CTRL+C and paste as value.

    I created a macro for this in the begining, but this macro now runs over 20 mins!!! (if i do it with hand with the above mentioned way, then it is only 5 mins :) )

    My question is does somebody know a better way to do this? (faster?) My macro is pretty simple it does what i do with hand, but is really slow... Maybe i miss something. In my way of thinking this should not take more then 2 mins to create with macro on all tabs. 

    If somebody has a better solution for this than this snapshot from this macro, please share with me.

    Many thanks!

    Sub Partcode_button()
    Dim StartTime As Double
    Dim SecondsElapsed As Double
    
    'Remember time when macro starts
      StartTime = Timer
        
        Dim wsThis As Worksheet
        Dim lRow As Long, NewRw As Long, i As Long, uccsosor As Long
        
        Set wsThis = ThisWorkbook.Sheets("TAB1")
        NewRw = 2
        With wsThis
           lRow = .Range("A" & .Rows.Count).End(xlUp).Row
            For i = 2 To lRow
                .Range("AA" & NewRw).Value = .Range("A" & NewRw).Value & .Range("B" & NewRw).Value & .Range("C" & NewRw).Value
                NewRw = NewRw + 1
            Next i
        End With
        
        Set wsThis = ThisWorkbook.Sheets("TAB2")
        NewRw = 2
        With wsThis
           lRow = .Range("A" & .Rows.Count).End(xlUp).Row
            For i = 2 To lRow
                .Range("AI" & NewRw).Value = .Range("A" & NewRw).Value & .Range("B" & NewRw).Value & .Range("C" & NewRw).Value
                NewRw = NewRw + 1
            Next i
        End With
            
        Set wsThis = ThisWorkbook.Sheets("TAB3")
        NewRw = 2
        With wsThis
           lRow = .Range("A" & .Rows.Count).End(xlUp).Row
            For i = 2 To lRow
                .Range("AJ" & NewRw).Value = .Range("A" & NewRw).Value & .Range("B" & NewRw).Value & .Range("C" & NewRw).Value
                NewRw = NewRw + 1
            Next i
        End With
        
     'continue with this same on the rest of the tabs  
        
        
    'Determine how many seconds code took to run
      SecondsElapsed = Round(Timer - StartTime, 2)
    
        
        MsgBox "Data imported, everything went fine! Runtime: " & SecondsElapsed & " seconds."
          
        
    End Sub


    Tuesday, September 15, 2015 9:26 PM

Answers

  • Re:  fill a range

    This ought to be faster...
    '---

        Set wsThis = ThisWorkbook.Sheets("TAB1")
        NewRw = 2
        With wsThis
          lRow = .Range("A" & .Rows.Count).End(xlUp).Row
          With .Range(.Range("AA" & NewRw), .Range("AA" & lRow))
           .Formula = "=A2&B2&C2"
           .Value = .Value
          End With
        End With
    '---

    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    Wednesday, September 16, 2015 1:24 AM
  • Sorry my bad :(

     .Formula = "=LEFT(H2,1)" works :) i have to use , instead of ;

    Saturday, September 19, 2015 7:39 PM

All replies

  • Re:  fill a range

    This ought to be faster...
    '---

        Set wsThis = ThisWorkbook.Sheets("TAB1")
        NewRw = 2
        With wsThis
          lRow = .Range("A" & .Rows.Count).End(xlUp).Row
          With .Range(.Range("AA" & NewRw), .Range("AA" & lRow))
           .Formula = "=A2&B2&C2"
           .Value = .Value
          End With
        End With
    '---

    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    Wednesday, September 16, 2015 1:24 AM
  • Hi hlpbob,

    This is the forum to discuss questions and feedback for Microsoft Excel, I'll move your question to the MSDN forum for Excel

    http://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev&filter=alltypes&sort=lastpostdesc

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    Regards,

    Emi Zhang
    TechNet Community Support


    Please remember to mark the replies as answers if they help, and unmark the answers if they provide no help. If you have feedback for TechNet Support, contact tnmff@microsoft.com.

    Wednesday, September 16, 2015 6:26 AM
  • Hello Jim,

    Your script will do it in 0,03sec on 4000 lines, while the old one was about 10 mins :) MANY THANKS!

    I try to improve and i changed the .Formula line to this:

     .Formula = "=LEFT(H2;1)"

    But with this i receive a 400 error (no error message), Do you know how can i implement the LEFT function as well? (i thought replaceing the =A2&B2... with the =LEFT will do it, but not :(

    Many thanks for the help in advance!

    Saturday, September 19, 2015 7:30 PM
  • Hello Emi,

    Thanks for that! Sorry that i posted in wrong topic.

    Saturday, September 19, 2015 7:30 PM
  • Sorry my bad :(

     .Formula = "=LEFT(H2,1)" works :) i have to use , instead of ;

    Saturday, September 19, 2015 7:39 PM
  • Re:  formula error with VBA

    Does...    =LEFT(H2;1)   ...return a value when entered manually on the worksheet?
    In my part of the world (USA), a comma is required not a semicolon.

    '---
    Jim Cone
    • Edited by James Cone Sunday, September 20, 2015 2:27 PM
    Sunday, September 20, 2015 2:26 PM
  • Hi Jim,

    As far as I know, the list separator is different in the different country. We can also set it via Control Panel\Clock, Language, and Region->Formats->Additional settings->List separator.

    Hope it is helpful.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, September 21, 2015 9:09 AM
    Moderator
  • If you turn the rows/columns into a table (format as table) then add a formula to concatenate the data into D1, when you press <enter> the table will be expanded to add column D and the formula automagically copied down to all rows. It's pretty slick, takes no coding, and works like a charm.
    Monday, September 21, 2015 2:05 PM