Excel Macro to concatenate

Question

Need help in creating an Excel Macro.I have an Excel sheet.The Excel sheet is not consistent. I am planning to make it uniform and structured.

Eg.

  A            B            C         D
1 test      tester         tester
2 hai       test
3 Bye       test           tested
4 GN        test           tested    Fine

  A            B            C         D
1 test      testertester   
2 hai       test
3 Bye       testtested     
4 GN        testtestedFine 

Basically I have to find the last cell where element is placed so based on that I can write my CONCATENATE funciton.

In this case it would be Column D and hence my concatenate function would have been =CONCATENATE(B1,C1,D1) Again I would like the result to be in B1 but not a problem if I have to hide.

Can anyone help me in doing this?

Answer 1

You could use the following VBA function which joins (concatenates) the values from an arbitrary range of cells, with an optional delimiter.

Public Function Join(source As Range, Optional delimiter As String)
    Dim text As String
    Dim cell As Range: For Each cell In source.Cells
        If cell.Value = "" Then GoTo nextCell

        text = text & cell.Value & delimiter

nextCell:
    Next cell

    If text <> "" And delimiter <> "" Then
        text = Mid(text, 1, Len(text) - Len(delimiter))
    End If

    Join = text
End Function

For an example of how to use the function, enter =JOIN(A1:D1) into a cell anywhere on the spreadsheet.

Answer 2

=B1&C1&D1

or

Adam's function that I have optimized.

Function Join(source As Range, Optional delimiter As String) As String
'
' Join Macro
' Joins (concatenates) the values from an arbitrary range of cells, with an optional delimiter.
'

'optimized for strings
'   check len is faster than checking for ""
'   string Mid$ is faster than variant Mid
'   nested ifs allows for short-circuit
'   + is faster than &

    Dim sResult As String
    Dim oCell As Range

    For Each oCell In source.Cells
        If Len(oCell.Value) > 0 Then
            sResult = sResult + CStr(oCell.Value) + delimiter
        End If
     Next

    If Len(sResult) > 0 Then
        If Len(delimiter) > 0 Then
            sResult = Mid$(sResult, 1, Len(sResult) - Len(delimiter))
        End If
    End If

    Join = sResult
End Function