Excel Macro

[EBenoit84]

Hello,

I am looking for an Excel 2003 macro that will merge any column that has a header (starting at B1:Last column with a header), trim and intrim (to remove the blanks), then do like a text to column split on the ' ' (space).

Example:

A B C D E

A C E

B D

I would like it to first merge the fields adding a space in between:

A B C D E
A C E
B D

Trim and Intrim the characters/numeric values:

A B C D E
A C E
B D

And then split them back apart on the space:

A B C D E

A C E

B D

Even if the text to column split part is done in a seperate macro, that would still be fine.

If this is possible please let me know.

Thanks in advance.

 

[SkipVought]

Seems like the result you are showing is simply using DELETE Shift Left on empty cells.



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[EBenoit84]

It is to an extent, but a macro would be better because there is no telling how much data might be in the spreadsheets. Even a macro for a delete shift left would work just fine.

 

[AnotherHiggins]

Here's a macro I put together a while back. It gets you almost all the way there - this will combine all columns for whatever cells you select.

Note: Only select the cells in the left-most column for the cells that you want combined!

Sub CombineColumnsForSelectedRows()
'*********************************************************************
'   Written By:     AnotherHiggins
'   Written On:     2008-03-26
'   Purpose:        combines selected cells with all populated cells
'                   to the right of them
'*********************************************************************

    Dim strMsgPrompt                         As String
    Dim strMsgTitle                          As String
    Dim strInputPrompt                       As String
    Dim strInputTitle                        As String
    Dim strMyDelimiter                       As String

    strMsgPrompt = Chr(9) & "This action cannot be undone!" & _
            Chr(10) & _
            Chr(10) & "You are about to combine cells in all selected rows." & _
            Chr(10) & _
            Chr(10) & "Are you sure you want to continue?"
    strMsgTitle = "Combine Cells Alert!"
    strInputPrompt = "Please type in what character(s), if any," & _
            Chr(10) & "you would like inserted between cell values." & _
            Chr(10) & "A popular choice is a comma followed by a space ("", "")" & _
            Chr(10) & _
            Chr(10) & "If you do not want any text inserted," & _
            Chr(10) & "you can just leave the box empty."
    strInputTitle = "Delimiter Input"

    MyResponse = Eval("MsgBox (chr(9) & 'This action cannot be undone! " & _
     "@You are about to combine cells in all selected rows.'" & _
     " & chr(10) & chr(10) & 'Are you sure you want to continue?@@', " & _
     "4, 'Combine Cells Alert!')")

    'MyResponse = MsgBox(strMsgPrompt, vbYesNo + vbDefaultButton2, strMsgTitle)

    If MyResponse = vbNo Then GoTo AnsweredNo

    strMyDelimiter = InputBox(strInputPrompt, strInputTitle)

    Application.ScreenUpdating = False
    For Each Cell In Selection
        For i = 1 To ActiveSheet.UsedRange.Columns.Count - ActiveCell.Column
            Cell.Value = Cell.Value & strMyDelimiter & Cell.Offset(, i).Value
            Cell.Offset(, i).Clear
        Next i

        '   Get rid of any leading or trailing spaces -
        '*******************************************************************************
        '   Replacing Chr(160) with Chr(32) before doing trim based
        '   on code by David McRitchie 2000-07-03 mod 2000-08-16
        Cell.Replace what:=Chr(160), replacement:=Chr(32), _
                Lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
        Cell.Value = Application.Trim(Cell.Value)

    Next Cell

    Application.ScreenUpdating = True

AnsweredNo:
End Sub

Just type in a space for your delimiter in the input box that pops up in the code above.

Record a macro of yourself doing a Text-to-Columns, slap it onto the end of the code above, and you'll be done!

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.