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

[gustaf111]

Hello,

I have a problem that I can not solve ...

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


Please help! Gustaf

 

[Bong]

strA=""
for each c in [a1:a22].cells
  strA=strA & c.value
next

_________________
Bob Rashkin

 

[SkipVought]

hi,

I'd design this as a user defined function. copy this to a MODULE and use as you would any other spreadsheet function...

Function ConcatValues(rng As Range) As String
  Dim r As Range

  For Each r In rng
    ConcatValues = ConcatValues & r.Value
  Next
End Function

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[nicitalia]

Yea I agree with Skip. Once you put your function in a cell you'll have to select the range in which the words to form the string are contained and the work is done!

 

[jges]

How are you treating empty cells?
In your example, it appears that cell B1 is empty but you want whitespace in your output string.

 

[gustaf111]

Hello all,

Thanks for all comments! I write wromg, it should be A1:Z1!

Sorry!

 

[SkipVought]

If you use the UDF I created, the range need not be hard coded.

The range is a reference to the function and it can be ANY range you choose.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[gustaf111]

Thanks!! Do you know anything about my other problem (Excel VBA write protected)?