Text to Columns Macro

[melewie]

Hi All,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I’m having a problem building a error trap in a "simple" macro. I want the macro to bring up a message box if there is no column selected. But having trouble making this happen<o></o>
i.e. <o></o>
<o></o>
If Selection = ISNT A FLAMING COLUMN Then Goto Error_Trap

Application.CutCopyMode = False
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1)

Exit Sub

Error_Trap:
MsgBox "HI"

Any help would be great.

 

[hiker95]

melewie,


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Adding the Macro
1. Copy the below macro, by highlighting the macro code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub CheckSelection()
' hiker95, 11/19/2009
Dim MaxRows As Long, MyRows As Long, MyColumns As Long

MaxRows = Rows.Count
MyRows = Selection.Rows.Count
MyColumns = Selection.Columns.Count

With ActiveSheet
  If MyRows <> MaxRows Or MyColumns > 1 Then
    GoTo Error_Trap
  Else
    Application.CutCopyMode = False
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
      TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
      Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
      :=Array(1, 1)
  End If
End With

Exit Sub
Error_Trap:
MsgBox "HI"
End Sub

Try selecting different ranges, and then run the "CheckSelection" macro.

The macro does not check to see if there is any data in a selected column.

 

[mikerickson]

There are two ways to approach this. You could test if the user has selected a single column

If Selection.Columns.Count = 1 And Selection.Cells.Count = ActiveSheet.Rows.Count Then
    MsgBox "A whole single column is selected"
End If

or you could note that there is always an Active Cell and use the whole column of that cell

With ActiveCell.EntireColumn
     Rem do stuff
End With