Macro/VBA question

[dracer]

Hi there. I'm trying to combine a bunch of cells in the same row into one formula with a pipe | in between the numbers. eg A1&"|"&A2&"|"&A3 etc. Can this be automated so this can be done automatically from the first row in a column with a value until the last row in a column with data. Like A1 to A83. I've been doing this manually. Thanks

 

[cerfani]

bunch of cells in the same column you mean?

Use a macro...

Sub CombineCells()
    Dim result As String
    Dim row As Long, endRow As Long
    endRow = Cells(Rows.Count, 1).End(xlUp).Row
    For row = 1 To endRow
        result = result & Cells(row, 1).Value
        If Not row = endRow Then
            result = result & " | "
        End If
    Next row

    Range("B1").Value = result
End Sub

 

[dracer]

Thanks!!! This is incredible. Saves me a lot of work down the line. One more question. Is there a place where I can save my various codes so I can access them. They are usually attached to the workbooks, but I'm wondering if I can see many marcos at once.

 

[cerfani]

No problem... and yeah, this is what the Personal workbook is for. This workbook is programmed to open with excel but it is hidden in the background. On this workbook you should save all your macros that you use frequently. If you go into the vba editor you will see it in the project list. You can additionaly setup hot keys for these macros in the workbook to call by key press or even better you can make buttons on your ribbon to call them.

 

[dracer]

Yes. I see it. Thanks a lot!