Sort The Date at the center of string with VBA

[geniusideas]

Hi, I need to sort date ascending or descending but the problem now the date location at the center or string for example 09-02-18012013-86653-A wherethe date is 18012013. This data at column A where column B to G got other data.Anyone here got idea how to do it with VBA. Thanks

 

[Claus Busch]

Hi,

Am Wed, 13 Feb 2013 06:19:01 -0800 (PST) schrieb geniusideas:

Hi, I need to sort date ascending or descending but the problem now the date location at the center or string for example 09-02-18012013-86653-A where the date is 18012013. This data at column A where column B to G got other data.Anyone here got idea how to do it with VBA. Thanks

with helper column in H (suit if you have headers):

Sub SortDate()
Dim LRow As Long
Dim intD As Integer, intM As Integer, intY As Integer
Dim rngC As Range

LRow = Cells(Rows.Count, 1).End(xlUp).Row
For Each rngC In Range("A1:A" & LRow)
intD = Mid(rngC, 7, 2)
intM = Mid(rngC, 9, 2)
intY = Mid(rngC, 11, 4)
rngC.Offset(0, 7) = DateSerial(intY, intM, intD)
Next
Range("A1:H" & LRow).Sort key1:=Range("H1"), _
order1:=xlAscending, Header:=xlNo
Columns("H").ClearContents

End Sub


Regards
Claus Busch