CODEWITHSUNDEEP
excelvba
Trevor
Trevor

Reputation: 35

Text to column Macro glitch

I am trying to use this macro as a shortcut to using the Text To Column prompt. I highlight the column or group of vertical cells in question and when running the macro the message box accepts a one character delimiter. The problem occurs when all three of these things happen.

  1. It is the first time I am running the macro in the workbook (If I run the macro again the glitch does not occur)

  2. I highlight an entire column

  3. There are blank cells above the first cell that needs to be parsed.

For example, if I have the text "123-456" in cell D10, I highlight all of column D and run the macro delimiting by "-" it parses the data into D1 and E1. If I delete the info in row 1 and try it again it works I except parsing the data into D10 and E10

Not sure how to troubleshoot this otherwise.

Sub Txt2Clm()
'
' Txt2Clm Macro
'

'

    mbox = InputBox("Text-To-Columns", "Quick Step", "Enter Delimiter")

    Selection.TextToColumns , DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :=mbox, FieldInfo:=Array(Array(1, 2), Array(2, 2)), TrailingMinusNumbers:=True


End Sub

Expected results should be how Text to Column typically works.

Upvotes: 1

Views: 607

Answers (2)

Cyril
Cyril

Reputation: 6829

Okay, in cutting out a lot of the falses, etc., I have this code which appears to work, not being bound to your fieldinfo array:

Sub ttc()
    mbox = InputBox("Text-To-Columns", "Quick Step", "Enter Delimiter")
    Selection.TextToColumns , DataType:=xlDelimited, Other:=True, OtherChar:=mbox
End Sub

Tested on some quick data... before :

enter image description here

and after:

enter image description here

Edit1:

Going to take a look at avoiding carrying through with Selection in the TTC, similar to the other post I linked in the comments...

Give this a shot to see if specifying the starting/ending row won't resolve that issue:

Sub TTC()
    mbox = InputBox("Text-To-Columns", "Quick Step", "Enter Delimiter")
    Dim sr As Long, er As Long, c As Long
    sr = Selection.Row 'start row
    er = Selection.Rows.Count + sr - 1 'end row
    c = Selection.Column 'column
    Range(Cells(sr, c), Cells(er, c)).TextToColumns , DataType:=xlDelimited, Other:=True, OtherChar:=mbox
End Sub

Upvotes: 0

tigeravatar
tigeravatar

Reputation: 26650

Give this a try. It includes a lot of error checking and data validation to make sure it can only Text-To-Columns on valid data:

Sub Txt2Clm()

    Dim rValues As Range
    Dim rArea As Range
    Dim sDelim As String

    'Evalute selected range for valid data that can have text to columns performed on it (no blanks, no formulas)
    If Selection.Cells.Count = 1 Then
        If Selection.HasFormula = False And Len(Selection.Value) > 0 Then Set rValues = Selection
    Else
        'Only get constant values from first column (text to columns can only work on a single column at a time)
        On Error Resume Next
        Set rValues = Selection.Resize(, 1).SpecialCells(xlCellTypeConstants)
        On Error GoTo 0
    End If

    If Not rValues Is Nothing Then
        'Prompt for delimiter
        sDelim = InputBox("Text-To-Columns", "Quick Step", "Enter Delimiter")
        If Len(sDelim) = 0 Then Exit Sub    'Pressed cancel
        If Len(sDelim) > 1 Then
            MsgBox "Delimiter must be a single character"
            Exit Sub
        End If

        Application.DisplayAlerts = False   'Prevent DisplayAlerts to suppress potential "Override existing data?" prompt
        For Each rArea In rValues.Areas
            rArea.TextToColumns rArea, xlDelimited, xlTextQualifierDoubleQuote, False, Other:=True, OtherChar:=sDelim
        Next rArea
        Application.DisplayAlerts = True
    Else
        MsgBox "No cells containing valid data selected."
    End If

End Sub

Upvotes: 1

Related Questions