JohnnyPicnic
New Member
- Joined
- Feb 8, 2021
- Messages
- 17
- Office Version
- 2019
- Platform
- Windows
Hello, I'm using VBA to concatenate two columns with a formula. I then want to delete the two original columns and only keep the now column. I can get almost all the way there but I can't get my new column to keep it's values.
Original
Result
Expected Result
VBA Code:
Dim LastRow As Long
With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
wb.Worksheets(1).Range(Range("A1"), Range("A1").End(xlDown)).NumberFormat = "mm/dd/yyyy hh:mm:ss AM/PM"
Range("B:B").Copy
Range("C:C").Insert
wb.Worksheets(1).Range(Range("C1"), Range("C1").End(xlDown)).Formula = "=Text(A1, ""mm/dd/yyyy"") & "" "" & Text(B1, ""hh:mm:ss"")"
Range("C1", "C" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
Range("C1") = "Date"
Range("C:C").Copy
Range("C:C").PasteSpecial xlPasteValues
Columns(1).EntireColumn.Delete
Columns(1).EntireColumn.Delete
Original
Result
Expected Result