Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy/Text To Columns/Transpose in VBA
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy/Text To Columns/Transpose in VBA
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? " wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy/Text To Columns/Transpose in VBA
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy/Text To Columns/Transpose in VBA
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. wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|