VBA Text to column code clean up

Donai

Well-known Member
Joined
Mar 28, 2009
Messages
543
Hi, could someone please help me clean this code up, i am using delimiter to change Date Text to number

Code:
Sub Macro1()
'
' Macro1 Macro
'

'
    Range("G6").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.TextToColumns Destination:=Range("G6"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
End Sub
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Hi. I don't think it can be cleaned up much beyond

Code:
Sub Macro1()
'
' Macro1 Macro
'

'
Dim LR As Long
LR = Range("G" & Rows.Count).End(xlUp).Row
Range("G6:G" & LR).TextToColumns Destination:=Range("G6"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
    :=Array(1, 1), TrailingMinusNumbers:=True
End Sub
 

Donai

Well-known Member
Joined
Mar 28, 2009
Messages
543
Vog when i ran the code the highlighed dates moved to the left and the remaining to the right, does not look right?


Excel Workbook
G
5Start Date
629/04/2009
74/06/2009
89/08/2010
922/03/2011
1022/03/2011
113/04/2011
127/07/2011
133/08/2011
143/08/2011
1522/03/2011
Office_Address_List
 

Donai

Well-known Member
Joined
Mar 28, 2009
Messages
543

ADVERTISEMENT

Did your original code work as expected?

Orignal code produced the same result, this was a recorded macro all i did was select the cells which house the dates and then did Text to columns-Delimiter, Enter, Enter, Finish, works when i do it manually, so why doesnt recorded macro not work?
 

GTO

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154
Vog when i ran the code the highlighed dates moved to the left and the remaining to the right, does not look right?

Hi Donai,

Could you show us the original data before its split? Does it come from a textfile or ???

Mark
 

Donai

Well-known Member
Joined
Mar 28, 2009
Messages
543
Hi Donai,

Could you show us the original data before its split? Does it come from a textfile or ???

Mark

Hi Mark, this is the original data, this data came from MS Access

Excel Workbook
G
5Start Date
629/04/2009
706/04/2009
808/09/2010
922/03/2011
1022/03/2011
1104/03/2011
1207/07/2011
1308/03/2011
1408/03/2011
1522/03/2011
Office_Address_List
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Why do you need to convert those? They look like UK-style dates (dd/mm/yyyy) to me.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,522
Messages
5,529,330
Members
409,863
Latest member
stacy09
Top