Excel VBA copy contents of cells A1:A22 to string variable

[gustaf111]

Hello all,

I would like to copy the contents of cells A1 to A22 to a string variable. The cell are not merged becuase I would like to design the contents better (there are several rows).

Ex:

A B C D E .....
1 Hello World

The string I would like to have is:
String = Hello World

Cheers Gustaf

 

[VoG]

Maybe like this

Sub test4()
Dim s As String
s = Join(Application.Transpose(Range("A1:A22")), " ")
MsgBox s
End Sub

 

[gustaf111]

Thanks VoG!

I would like also to have all cells on a row, but it does not work just to replace with:

s = Join(Application.Transpose(Range("A1:G1")), " ")

 

[VoG]

Try

Sub test4()
Dim s As String
s = Join(Application.Transpose(Application.Transpose(Range("A1:G1"))), " ")
MsgBox s
End Sub

 

[gustaf111]

Thanks! Super!

 

[gustaf111]

Re: Excel VBA copy contents of cells A1:W1 to string variable

The code does not exactly what I want, I try to explain:

I would like to get a string of all columns from column A1 to W1. But these to strings will not be the same:

1) If I start the input on column A1 and add spaces to column F1 and there write hello.

2) If I start the input on column F1 and write hello.

String number 1 will look like:
B1
hello (not correct postion, should be there string number 2 is)

string number 2 will look lime:
F1
hello (correct position)

Please Help!
Cheers Gustaf