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.
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:
Second, run the macro:
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.
Finally, apply some word wrap, resize the dimensions, and voila!
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