Re: [ExcelVBA] Transpose and Insert Commas, I don't have a clue how to do this

 

Dear Eric

This demonstrates how you would do it up to the point where you want to display in Excel :-

Sub TransposeColumnA()
Dim nRow As Long, nRowCount As Long
Dim vInputRectangle As Variant, vInputColumn() As Variant
Dim vTransposedColumn As Variant
'
    vInputRectangle = Range("a1:a15000").Value
    nRowCount = UBound(vInputRectangle)
    ReDim vInputColumn(1 To nRowCount)
    For nRow = 1 To nRowCount
        vInputColumn(nRow) = Trim(vInputRectangle(nRow, 1))
    Next nRow
    vTransposedColumn = Join(vInputColumn, ",")
    Cells(1, 2).Value = vTransposedColumn
    Debug.Print Len(vTransposedColumn), Len(Cells(1, 2).Value)
End Sub

The assignment to Cells(1,2) does not error, it truncates to the magic 32767. 

You did not tell us how you are going to use this in Excel, surely nobody is going to sit and read it, so it must be input for another system ?

Maybe saving as a TXT file is the answer.

Derek +++ 

>________________________________
> From: "1z@compuserve.com" <1z@compuserve.com>
>To: ExcelVBA@yahoogroups.com
>Sent: Friday, 16 March 2012, 12:44
>Subject: Re: [ExcelVBA] Transpose and Insert Commas, I don't have a clue how to do this
>
>
> 
>
>Good catch Paul!
>
>Lisa
>
>Sent: Thu, Mar 15, 2012 10:25 pm
>Subject: Re: [ExcelVBA] Transpose and Insert Commas, I don't have a clue how to do this
>
>We can probably help you do it, but I think you're going to have a problem:
>F you're using Excel 2010, then the total number of characters you can display
>n a cell is 32,767
>only 1024 can display in the cell, but 32,767 will display in the formula bar)
>Now, if you have 15,000 rows, and then numbers are ONLY two digits long, then
>ou'll be ok,
>ecause that's only 30,000 characters.
>ut if more than 2,767 of them are more than 2 characters long... you're in
>rouble...
>based on your samples, you'll only be able to concatenate 4,000 of your 15,000
>ows...
>So, maybe you need to look at another way of handling this...
>Paul
>----------------------------------------
>Do all the good you can,
>y all the means you can,
>n all the ways you can,
>n all the places you can,
>t all the times you can,
>o all the people you can,
>s long as ever you can." - John Wesley
>----------------------------------------
>
>[Non-text portions of this message have been removed]
>
>
>
>
>

[Non-text portions of this message have been removed]

__._,_.___

Reply to sender | Reply to group | Reply via web post | Start a New Topic

Messages in this topic (14)

Recent Activity:

• New Members 14

Visit Your Group

----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.

----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA

----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com

----------------------------------

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use

.

__,_._,___