vba concatenate with space

[excel01noob]

I have a sheet with data in several columns.
I want to use column A data (month) with colum D data (Product) and column E (billing ID)

How do I concatenate these 3 columns and have the data separated by space?
something like this

March pencil B34009

 

[excel01noob]

found it, I was missing the double quotation mark

"" ""

 

[Rick Rothstein]

You didn't provide a lot of information about your data or code, so I don't know if this is the solution you came up with or not. Here is a non-looping solution for you (outputs to Column H) assuming your data starts in Row 2...

Sub Test()
  Dim LastRow As Long
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Range("H2:H" & LastRow) = Evaluate(Replace("A2:A#&"" ""&D2:D#&"" ""&E2:E#", "#", LastRow))
End Sub

 

[excel01noob]

Thank you Rick!

 

[excel01noob]

how would I make it work the concat if I want to add into column P (assuming header):

-letter C and
-account number (it's a 8 digits number) from column H until last row with data

C12345678

 

[Rick Rothstein]

Are you wanting this added to the macro I gave you earlier or is this supposed to be a separate macro?

 

[excel01noob]

Hi

It will be part of the same macro

 

[Rick Rothstein]

-letter C and
-account number (it's a 8 digits number) from column H until last row with data

It will be part of the same macro

Wait a minute!!! I just noticed that my previous code outputs to Column H and what it outputs is not an 8-digit number?!!??!? Please explain.

 

[excel01noob]

the output in the first macro was not in column H, I have amended the code to the correct column (it is column O)

 

[Rick Rothstein]

Give this a try...

Sub Test()
  Dim LastRow As Long
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Range("O2:O" & LastRow) = Evaluate(Replace("A2:A#&"" ""&D2:D#&"" ""&E2:E#", "#", LastRow))
  Range("P2:P" & LastRow) = Evaluate("TEXT(H2:H" & LastRow & ",""C00000000"")")
End Sub