Concatenate cells with leading zeros vba

[podder]

Hi,
Can anybody help, I require 5 cells in a row to be joined together as one, however, one cell has a leading zero (which must remain) and each cell must be separated by a space.
I have imported the data and cant change the cells to text (for leading zeros), as for this to work I must physically enter the zeros.

Does anyone have an idea how this can be achieved using vba

[Dave]

Maybe...

Sub test()
Dim Lastrow As Integer, Rng As Range, R As Range
Dim STR As String, Oe As String
With Sheets("sheet1")
    Lastrow = .Range("A" & .Rows.Count).End(xlUp).Row
End With
Set Rng = Sheets("Sheet1").Range("A1:A" & Lastrow)
For Each R In Rng
STR = STR & R.Text & " "
Next R
STR = Left(STR, Len(STR) - 1)
MsgBox STR
End Sub

HTH. Dave

[podder]

Maybe...

Sub test()
Dim Lastrow As Integer, Rng As Range, R As Range
Dim STR As String, Oe As String
With Sheets("sheet1")
    Lastrow = .Range("A" & .Rows.Count).End(xlUp).Row
End With
Set Rng = Sheets("Sheet1").Range("A1:A" & Lastrow)
For Each R In Rng
STR = STR & R.Text & " "
Next R
STR = Left(STR, Len(STR) - 1)
MsgBox STR
End Sub

HTH. Dave

Thanks for the reply Dave,
The code you sent me only shows the output in a window and does not retain it in a cell.
Also, each row needs to be in a separate cell

Thanks

[Dave]

Yes it only produces an output msgbox to see if the output is correct. U need to test the code and see if it correctly outputs what U want. Care to share where and how U want to place your output? Dave

[podder]

Yes it only produces an output msgbox to see if the output is correct. U need to test the code and see if it correctly outputs what U want. Care to share where and how U want to place your output? Dave

Yes, Sorry Dave
I want the output to be placed in column A - the output msgbox is correct - Thanks

[Dave]

Need a bit more info.... where is the input data located (rows and column) and what row(s) do you want the output? Dave

[podder]

Need a bit more info.... where is the input data located (rows and column) and what row(s) do you want the output? Dave

Columns are B to E and rows are to the last row of data located in column B are what need to be concatenated and the output in Column A
Hope this is enough information
Also, would it be possible to miss a column using the same idea - as in Concatenate columns B, C, D, and F and input into Column A

[Dave]

So U actually want to concatenate non-contiguous columns not " I require 5 cells in a row to be joined together as one". This is fairly basic. Dave

Sub test()
Dim Lastrow As Integer, Cnt As Integer
With Sheets("sheet1")
    Lastrow = .Range("B" & .Rows.Count).End(xlUp).Row
End With
For Cnt = 1 To Lastrow
Sheets("Sheet1").Cells(Cnt, "A") = CStr(Sheets("Sheet1").Cells(Cnt, "B")) & " " _
& CStr(Sheets("Sheet1").Cells(Cnt, "C")) & " " & CStr(Sheets("Sheet1").Cells(Cnt, "D")) & " " _
& CStr(Sheets("Sheet1").Cells(Cnt, "F"))
Next Cnt
End Sub

[podder]

So U actually want to concatenate non-contiguous columns not " I require 5 cells in a row to be joined together as one". This is fairly basic. Dave

Sub test()
Dim Lastrow As Integer, Cnt As Integer
With Sheets("sheet1")
    Lastrow = .Range("B" & .Rows.Count).End(xlUp).Row
End With
For Cnt = 1 To Lastrow
Sheets("Sheet1").Cells(Cnt, "A") = CStr(Sheets("Sheet1").Cells(Cnt, "B")) & " " _
& CStr(Sheets("Sheet1").Cells(Cnt, "C")) & " " & CStr(Sheets("Sheet1").Cells(Cnt, "D")) & " " _
& CStr(Sheets("Sheet1").Cells(Cnt, "F"))
Next Cnt
End Sub

Dave it works great
Thanks for your help just what I needed

[Dave]

You are welcome. Thanks for posting your outcome. Dave