Add single space between every cell value

[VBABEGINER]

Hello All,

Below is my code. I've values from Col B to F and getting all this cell value in Col A cell. Now I'm looking to have space between every cell value. Can anyone here to guide code for the same. I really appreciate if provide me something on this..
Thanks..!!
Sincerely.. vbabeginer

Dim lstRw As Long
Dim lstCl As Long
Dim i As Long
Dim j As Long
Dim result As Variant
lstRw = Sheets("Output").Range("A" & Rows.Count).End(xlUp).Row + 1

For i = 3 To lstRw
lstCl = Sheets("Output").Cells(lstRw, Columns.Count).End(xlToLeft).Column
For j = 2 To lstCl
result = result & Sheets("Output").Cells(i, j)
Next j
Sheet6.Range("A" & i) = result
result = ""
Next i

 

[Marc L]

Hi,​

​

any attachment ?!​

​

May be achieved with a single loop just using some Excel basics or even with a beginner level formula on some recent Excel versions …​

 

[VBABEGINER]

Hi,​

​

any attachment ?!​

​

May be achieved with a single loop just using some Excel basics or even with a beginner level formula on some recent Excel versions …​

No I cant provide any file

 

[VBABEGINER]

Anyone else, board members, pls assist

 

[Joe4]

No I cant provide any file

Your question seems straightforward enough, but your code does not seem to reflect your question that well (i.e. it sounds like you want to combine columns B-F and put in column A, suggesting that column A is empty, but your code is looking in column A for the last row of data, which doesn't make much sense!). So I do not know if the error is in your question or your code, or both.

In situations like this, it is often very helpful to see a small example of what the data structure looks like and your expected results. Even if you cannot provide the actual data you are working with, you can make up some sample "dummy" data and post that, along with the expected results. As the saying goes, "a picture says a thousand words!"

 

[footoo]

Perhaps :

result = result & Sheets("Output").Cells(i, j) &  " "

 

[VBABEGINER]

Your question seems straightforward enough, but your code does not seem to reflect your question that well (i.e. it sounds like you want to combine columns B-F and put in column A, suggesting that column A is empty, but your code is looking in column A for the last row of data, which doesn't make much sense!). So I do not know if the error is in your question or your code, or both.

In situations like this, it is often very helpful to see a small example of what the data structure looks like and your expected results. Even if you cannot provide the actual data you are working with, you can make up some sample "dummy" data and post that, along with the expected results. As the saying goes, "a picture says a thousand words!"

Hello Joe Sir.. Thanks for your reply. I agree with your point and from next time I'll try to create example and then will post.
Basically in below statement, values which are available in (for example) b3, c3, d3, e3, f3 are coming w.out space in A3. Although the solution provided by footoo Sir, is working here.. let me show example..

result = result & Sheets("Output").Cells(i, j)

A3 (output cell)	B3	C3	D3	E3	F3
MangoAppleJuiceLike itor not	Mango	Apple	Juice	Like it	or not

but now it is coming properly, after applying this.. & " "

i.e. Mango Apple Juice Like it or not

But thanks again Joe Sir for your support.. ?

 

[VBABEGINER]

Perhaps :

result = result & Sheets("Output").Cells(i, j) &  " "

Perfect. Thank You footoo Sir... working as per requirement.. Have a great day ahead..