Excel: Combine Cells in Selection

Often when responding to RFPs, I find myself copying and pasting out of PDFs into excel to create Table of Compliance. This often results in some donkey-work that people might find familiar: deleting the header and footer for every page, and sorting all the lines into cells. Problem is, copy and paste from a PDF reader includes a CR/LF at the end of every line, which means your long sentence or paragraph gets pasted into several cells.

Copy and paste from a PDF and sentences will be split into rows

This macro (which I have on a button in the ribbon) will combine all the selected cells into a single text string, and place it in the upper-left-most cell in the selection.

First, select the rows the merge:

Select the rows to merge

Second, run the macro:

Run the Macro. I have it located on the main tab of the ribbon

Third, delete the rows left behind. I toyed with the idea of deleting these rows right in the macro, but opted to leave them alone. If I did delete them, then you’d still have to remove them, and I certainly didn’t want to do that in the macro, in case you were merging cells in amongst other cells.

Delete the remaining rows

Finally, apply some word wrap, resize the dimensions, and voila!

Resize and Word Wrap

Here is the script. All suggestions, comments and corrections welcome.

Function ConcatinateAllCellValuesInRange(sourceRange As Excel.Range, seperator As String) As String
    Dim finalValue As String

    Dim cell As Excel.Range

    For Each cell In sourceRange.Cells
        finalValue = finalValue + seperator + CStr(cell.Value)
    Next cell

    ConcatinateAllCellValuesInRange = Trim(finalValue)
End Function

Sub CombineCellsInSelection()
    Dim ActSheet As Worksheet
    Dim SelRange As Range
    Dim finalValue As String
    Dim destination As Excel.Range

    Set ActSheet = ActiveSheet
    Set SelRange = Selection

    finalValue = ConcatinateAllCellValuesInRange(SelRange, " ")
    SelRange(1, 1).Value = finalValue

End Sub

Leave a Reply

Your email address will not be published.