Copy/Text To Columns/Transpose in VBA

[brett.kaplan]

Hi,

I have a string of numbers, each separated by a space, in a single
cell. I want to take that string, do a text-to-columns, and then
copy-paste special-transpose into a single column. I tried recording
this macro, but I have too many numbers and it will exceed the 256
column limit when I do text to columns. How can I do this through VBA?

Thanks!

Brett

 

[Guest]

VBA won't help you overcome the 256 column limit, per se.

I presume you want to take data that would be in columns beyond IV and put
it somewhere else? Where would that else be? On another sheet? On another
row on the same sheet - if so, how much further down the sheet (next row, or
way on down somewhere)?

Or you could wait for Excel 2007 with its 65000+ columns available?

 

[Dave O]

Land the cell pointer on a cell with the string of numbers, and run
this code. It writes everything between two spaces to a new cell
underneath the original cell.

Sub Parse()
Dim CellVal As String
Dim LastSpace As Long
Dim Entry As Variant
Dim NextRow As Long
Dim K As Long 'counter

CellVal = Trim(ActiveCell.Value) & " "

For K = (LastSpace + 1) To Len(CellVal)
If Mid(CellVal, K, 1) <> " " Then
Entry = Entry & Mid(CellVal, K, 1)
Else
LastSpace = K 'reset LastSpace column number
NextRow = NextRow + 1 'increment next row
ActiveCell.Offset(NextRow, 0).Value = Entry 'write to the next row
Entry = "" 'reset
End If
Next K

End Sub

 

[Dave Peterson]

If you're using xl2k or higher, you could use VBA's Split function.

Option Explicit
Sub testme()

Dim myArr As Variant
Dim myCell As Range

Set myCell = Worksheets("sheet1").Range("a1")

myArr = Split(Application.Trim(myCell.Value), " ")

myCell.Offset(0, 1).Resize(UBound(myArr) - LBound(myArr) + 1, 1).Value _
= Application.Transpose(myArr)

End Sub

There are different limits depending on what version of excel you're using.
xl2k has a limit of 5461(?) elements that can be passed to
application.transpose. xl2002+ doesn't have that restriction.