PDA

View Full Version : [SOLVED] Concatenate cells with leading zeros vba



podder
01-06-2018, 01:24 AM
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
01-06-2018, 06:43 AM
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
01-06-2018, 03:34 PM
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
01-06-2018, 08:34 PM
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
01-06-2018, 09:56 PM
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
01-07-2018, 07:50 AM
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
01-07-2018, 04:31 PM
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
01-07-2018, 06:14 PM
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
01-10-2018, 04:38 AM
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
01-10-2018, 07:00 AM
You are welcome. Thanks for posting your outcome. Dave