Paste value of formula

Purfleet · #1 09-03-2021, 08:12 PM

Okay, if i understand correctly i think you were trying to do too much - the Macro should be able to do it all

Attached are 2 macros

Join Active

will join the active cell to the cell to the right with a ": " in between and then delete the cell to the right

Code:

Sub JoinActive()

    ActiveCell = ActiveCell & ": " & ActiveCell.Offset(0, 1)

    ActiveCell.Offset(0, 1).Cells.ClearContents

End Sub

JoinAll

Will look at every cell in column b and join it to the cell to the right with a ": " in between - this one takes a few seconds as it is looking at 1million rows.

Code:

Sub JoinAll()

    Dim Col1 As Range 'left column to join
    Dim Col2 As Range 'right column to join
    Dim r1 As Range ' for looping
    
    Set Col1 = ActiveSheet.Range("B:B")
    Set Col2 = ActiveSheet.Range("c:c")
    
    For Each r1 In Col1
        If r1.Value <> "" Then
            r1 = r1 & ": " & r1.Offset(0, 1)
        End If
        
    Next r1

    Col2.Cells.ClearContents

End Sub

Both of these could easily be made more efficent and flexible but hopefully its a start

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
PASTE FORMULA from lookup table	ChrisOK	Excel Programming	19	04-22-2020 05:01 PM
cut/copy and paste won't paste	DennisWG	Word	1	03-13-2020 12:36 PM
Formula Paste Issue	rabend	Excel	3	06-02-2014 12:06 PM
Paste to a series of boxes with one paste?	BudVitoff	Misc	0	11-27-2013 02:51 PM
Paste Special: Copy and Paste Formatting Only?	tinfanide	Word	6	03-06-2013 12:21 AM