help with easy macro

[thepartydj]

I would like a macro that will go down one column deleting everything in each cell but the first 9 characters.

for example it will take a text cell with
10/3/2006 15:30:54
to
10/03/2006

I need this because I already have the data and I didn't format the column as date. Thanks

 

[erik.van.geit]

Hi,

try this

Sub test()
Dim rng As Range
Dim arr As Variant
Dim i As Long
Dim LR As Long

LR = Cells(Rows.Count, 1).End(xlUp).Row

Set rng = Range("A1:A" & LR)
arr = rng

    For i = 1 To rng.Rows.Count
        arr(i, 1) = Left(arr(i, 1), 9)
    Next i

rng = arr

End Sub

(not sure if this will work, you never know with date-stuff)

for more info about this technique see http://puremis.net/excel/code/053.shtml

kind regards,
Erik

 

[HalfAce]

I don't think the above code will give the desired results.
If all you're wanting is to format these as dates then wouldn't this do?
(Or am I misunderstanding?)

Sub FormatToDates()
Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row).NumberFormat = "mm/dd/yy"
End Sub

 

[HalfAce]

Got to thinking about this and (since Erik's seldom wrong) I'm wondering if you're not
wanting to just format as dates, but change the values - and appearance -
to actual dates (as in whole numbers only).
If that's the case then this may be of interest.
(Assumes you have header(s) in row 1)

Sub test2()Dim LR As Long
Dim aRng As Range, bRng As Range
LR = Cells(Rows.Count, 1).End(xlUp).Row
Set aRng = Range("A2:A" & LR)
On Error GoTo Quit
With Application
  .EnableEvents = False
  .ScreenUpdating = False
End With

Columns("B:B").Insert Shift:=xlToRight
Set bRng = Range("B2:B" & LR)
bRng.NumberFormat = "General"

With Range("B2")
  .Formula = "=ROUNDDOWN(A2,0)"
  .NumberFormat = "0"
  .AutoFill Destination:=Range("B2:B" & LR)
End With

With aRng
  .NumberFormat = "mm/dd/yy"
  .Value = bRng.Value
End With

Columns("B:B").Delete

Quit:
With Application
  .EnableEvents = True
  .ScreenUpdating = True
End With
End Sub

[EDIT:]
Changed a few of the cell references to keep the dates in their proper rows.
(Thanks goes to Erik for pointing that out.)