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
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Why do you need to convert those? They look like UK-style dates (dd/mm/yyyy) to me.
 
Upvote 0

Forum statistics

Threads
1,222,228
Messages
6,164,724
Members
451,912
Latest member
HMF009

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top