`4/19/2020 6:00:00 PM - this is what the cell has inside of it. I would like to remove the "6:00:00 PM" part but the only function I can find is the .REPLACE function which just eraces the whole cell and turns it blank. There is a different date for each cell so I can't just replace with it the same date every time.
With Columns("E:E")
.Replace What:="?*:00:00 PM=", Replacement:="?*:00:00 PM "
.Replace What:="?*:00:00 PM", Replacement:=""
End With
With Columns("H:H")
.Replace What:="?*:00:00 PM=", Replacement:="?*:00:00 PM "
.Replace What:="?*:00:00 PM", Replacement:=""
End With
If the values in column E are genuine date/times, then run:
Sub dropTime()
With Range("E:E").Cells
.NumberFormat = "m/d/yyyy "
End With
End Sub
If the values in column E are Text values, then run:
Sub dropTime2()
Dim rng As Range, r As Range, arr
Set rng = Intersect(Range("E:E"), ActiveSheet.UsedRange)
For Each r In rng
arr = Split(r.Text, " ")
If UBound(arr) = 2 Then
r.Value = arr(0)
r.NumberFormat = "m/d/yyyy "
End If
Next r
End Sub
Use the format()
function.
This answer will find the last row in column 4 ("D") and loop through from row 1 until lRow (last row), formatted every cell as defined.
Sub test()
Dim rng As Range, cell As Range
Dim lRow As Long
Dim wb As Workbook: Set wb = ThisWorkbook
Dim ws As Worksheet: Set ws = wb.Worksheets("Data")
lRow = ws.Cells(ws.Rows.Count, 4).End(xlUp).Row
Set rng = Range(Cells(1, 4), Cells(lRow, 4))
For Each cell In rng
cell = Format(cell, "dd/mm/yyyy")
Next cell
End Sub
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With