none
VBA - Transpose Array Vertical to Horizontal - Print in Immediate Window RRS feed

  • Question


  • Hi friends,

     

    I am trying to transpose a column of values.

     

    I would like to print it to the immediate window rather than in the worksheet.

     

    I have found ones that simply print the vertical column values, but that's not of much use as i can simply copy and paste to a text file to acheive the same result.

     

    I am trying to transpose Vertical to Horizontal as shown below.

     

    I've spent hours trying to get this to work, so back to basics now

     

     

     

    Sub TransposeArray()
    
     
    Dim MyArray As Range
    
    
    'MyArray = Range("B1:E1")
    
    
    Range("B1:E1").Formula = WorksheetFunction.Transpose(Range("A1:A4").Formula)
    
    
    'Debug.Print MyArray
    
    
    
    End Sub


    This is so that I can quickly copy the values and put it into another macro.

    Thank you for any ideas

     

    :)


    Cheers Dan :)

    Wednesday, November 23, 2016 12:00 PM

Answers

  • For example:

    Sub TransposeArray()
        Debug.Print Join(Application.Transpose(Range("A1:A4")))
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Dan_CS Wednesday, November 23, 2016 3:10 PM
    Wednesday, November 23, 2016 2:43 PM

All replies

  • For example:

    Sub TransposeArray()
        Debug.Print Join(Application.Transpose(Range("A1:A4")))
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Dan_CS Wednesday, November 23, 2016 3:10 PM
    Wednesday, November 23, 2016 2:43 PM
  • Hello Hans,

    Unbelievable - and I looked everywhere :(

     

    I was nearly nearly there with this line of thought

     

    Split(Join(Application.Transpose(ActiveSheet…..but then that also didn’t work

     

    Well that solves the problem yay

     

    Have a great day :)



    Cheers Dan :)


    • Edited by Dan_CS Wednesday, November 23, 2016 3:10 PM
    Wednesday, November 23, 2016 3:10 PM