You could use a UDF to concatenate the columns, then Copy > Paste Special > values
In a standard module
Option Explicit
Function Conc(rng As Range, Optional Separator As String = "")
Dim Cell As Range
For Each Cell In rng
If Cell <> "" Then
Conc = Conc & Separator & Cell
End If
Next
If Separator <> "" Then
Conc = Right(Conc, Len(Conc) - Len(Separator))
End If
Conc = WorksheetFunction.Trim(Conc)
End Function
Then in C5
=IF(ISERROR(conc(E5:H5," ")),"",conc(E5:H5," "))
Drag/Fill Down (or, in this example, double click the fill handle)
If you really need VBa to do this for you, then we could add 3 named ranges
"Start_Column"
"Start_Row"
"NumberOfColumns"
and put the starting parameters in these cells
(See the Names Manager).
By using Names the VBa will not be affected if you decide to rearrange these cells, just drag them to somewhere that suits.
Add this routine to the module
Sub ConcatenateColumnsByRow()
Dim LastRow As Long, NumberOfColumns As Long
Dim StartAddress As String, StartColumn As String, StartRow As String
Dim strFormula As String, strRowAddress As String
LastRow = ActiveSheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.Row - 1
StartAddress = ActiveCell.Address
'Use the named ranges to define concatenated Row.Address
StartColumn = Range("Start_Column")
StartRow = Range("Start_Row")
NumberOfColumns = Range("NumberOfColumns")
strRowAddress = Range(StartColumn & StartRow).Address(0, 1) _
& ":" _
& Range(StartColumn & StartRow).Offset(0, NumberOfColumns - 1).Address(0, 1)
strFormula = "=IF(ISERROR(conc(" & strRowAddress & ","" "")),"""",conc(" & strRowAddress & ","" ""))"
ActiveCell.Resize(LastRow - (ActiveCell.Row - 1), 1).Formula = strFormula
With Range(ActiveCell, Cells(LastRow, ActiveCell.Column))
.Copy
.PasteSpecial Paste:=xlPasteValues
End With
Range(StartAddress).Select
Application.CutCopyMode = False
End Sub
Select whatever cell you want the combined column to begin in then run the macro "ConcatenateColumnsByRow", this cell can be anywhere on the sheet, not necessarily C5
Hope this helps
Bookmarks