VBA script to set column to values not working

[JohnnyPicnic]

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.

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

 

[mrshl9898]

Strange, a near identical code tested ok here. Can't see much difference

Sub dostuff()

Dim wb As Workbook
Dim LastRow As Long
    
Set wb = ThisWorkbook

    With ActiveSheet
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With
    
      wb.Sheets("Sheet3").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.Sheets("Sheet3").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
End Sub

 

[JohnnyPicnic]

Well I guess i'm glad i'm not the only who can't see the problem
Right now my only solution is to run the vba without the

Range("C:C").Copy
Range("C:C").PasteSpecial xlPasteValues
      
Columns(1).EntireColumn.Delete
Columns(1).EntireColumn.Delete

Save the file, then open it again and delete the first two columns.

 

[Osvaldo Palmeiro]

Sub ConcatenateAB()
 Dim LR As Long
  Application.ScreenUpdating = False
  LR = Cells(Rows.Count, 1).End(3).Row
  Columns(3).Insert
  With Range("C2:C" & LR)
   .Value = Evaluate(.Offset(, -2).Address & "+" & .Offset(, -1).Address)
   .NumberFormat = "mm/dd/yyyy hh:mm:ss AM/PM"
  End With
  [A:B].Delete
  [A1] = "Date"
  Columns(1).AutoFit
End Sub

 

[JohnnyPicnic]

Sub ConcatenateAB()
Dim LR As Long
  Application.ScreenUpdating = False
  LR = Cells(Rows.Count, 1).End(3).Row
  Columns(3).Insert
  With Range("C2:C" & LR)
   .Value = Evaluate(.Offset(, -2).Address & "+" & .Offset(, -1).Address)
   .NumberFormat = "mm/dd/yyyy hh:mm:ss AM/PM"
  End With
  [A:B].Delete
  [A1] = "Date"
  Columns(1).AutoFit
End Sub

Thank you very much.