Results 1 to 5 of 5
  1. #1
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Splitting cells at a delimiter (Excel 2000)-MULTI-Splittin

    I'm having difficulty writing VBA code to do the following:
    After the user selects a column, I want the macro to loop through each cell, and split the cell at the first linebreak (chr(10)) character, then write the two pieces to columns P & Q of that same row. For instance, a cell containing:

    Line1
    Line2
    Line3

    will produce:

    Line1 in col P

    Line2
    Line3 in col Q

    I need to ignore cells without a chr(10) as well as empty cells, and somehow end execution even if an entire (infinite) column is selected.

    Any help on combining all of these wishes is appreciated.

    Alan

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Splitting cells at a delimiter (Excel 2000)-MULTI-Splittin

    Here is a part of a possible solution. It doesn't check whether a whole column has been selected.

    Sub SplitOnLineFeed()
    Dim rng As Range, aCell As Range
    Dim intPos As Integer
    ' Restrict selection to one column
    Set rng = Selection.Columns(1)
    For Each aCell In rng.Cells
    intPos = InStr(aCell, Chr$(10))
    If intPos > 0 Then
    aCell.Offset(0, 1) = Mid(aCell, intPos + 1)
    aCell = Left(aCell, intPos - 1)
    End If
    Next aCell
    End Sub

  3. #3
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Splitting cells at a delimiter (Excel 2000)-MULTI-

    Thank you Hans. I had tried the string manipulation functions, but not as efficiently as you had. The second problem of looping on all the cells in a column, even the mass of empty ones at the bottom, I was able to solve using the following tips I found:

    o How can I determine the last non-empty cell in a particular column?

    The statement below displays the address of the last non-empty cell in column A.

    MsgBox ActiveSheet.Range("A65536").End(xlUp).Address

    o The statement in the previous question doesn't work if cell A65536 is not empty.

    To handle that unlikely occurrence, use this code:

    With ActiveSheet.Range("A65536")
    If .Value <> "" Then
    MsgBox .Address
    Else
    MsgBox .End(xlUp).Address
    End If
    End With

    Alan

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Splitting cells at a delimiter (Excel 2000)-MULTI-

    In view of your objective relating to text cell content, it may be more efficient to select target cells using something like:

    For Each rngCell In Selection.EntireColumn.SpecialCells(xlCellTypeCons tants, xlTextValues)

    this way the user need only select a cell in the target column, and you don't need to determine the range, SpecialCells does it for you.
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Splitting cells at a delimiter (Excel 2000)-MULTI-

    Thanks John.
    Another new one to add to the arsenal ;-)

    Alan

Posting Permissions

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