Results 1 to 6 of 6

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

This is a discussion on Excel VBA copy contents of cells A1:A22 to string variable within the Excel Questions forums, part of the Question Forums category; Hello all, I would like to copy the contents of cells A1 to A22 to a string variable. The cell ...

  1. #1
    Board Regular
    Join Date
    Sep 2011
    Posts
    120

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

    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

  2. #2
    VoG
    VoG is offline
    MrExcel MVP
    Moderator
    VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    62,757

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

    Maybe like this

    Code:
    Sub test4()
    Dim s As String
    s = Join(Application.Transpose(Range("A1:A22")), " ")
    MsgBox s
    End Sub
    HTH, Peter
    Please test any code on a copy of your workbook.

  3. #3
    Board Regular
    Join Date
    Sep 2011
    Posts
    120

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

    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")), " ")


  4. #4
    VoG
    VoG is offline
    MrExcel MVP
    Moderator
    VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    62,757

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

    Try

    Code:
    Sub test4()
    Dim s As String
    s = Join(Application.Transpose(Application.Transpose(Range("A1:G1"))), " ")
    MsgBox s
    End Sub
    HTH, Peter
    Please test any code on a copy of your workbook.

  5. #5
    Board Regular
    Join Date
    Sep 2011
    Posts
    120

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

    Thanks! Super!

  6. #6
    Board Regular
    Join Date
    Sep 2011
    Posts
    120

    Default 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

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com