VBA script to set column to values not working

JohnnyPicnic

New Member
Joined
Feb 8, 2021
Messages
5
Office Version
  1. 2019
Platform
  1. 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.

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
2021-02-08_15-11-04.jpg
Result
2021-02-08_15-09-22.jpg
Expected Result
2021-02-08_15-10-28.jpg
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,709
Strange, a near identical code tested ok here. Can't see much difference


VBA Code:
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

New Member
Joined
Feb 8, 2021
Messages
5
Office Version
  1. 2019
Platform
  1. Windows
Well I guess i'm glad i'm not the only who can't see the problem :p
Right now my only solution is to run the vba without the
VBA Code:
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

Well-known Member
Joined
Feb 24, 2009
Messages
627
Office Version
  1. 365
Platform
  1. Windows
VBA Code:
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
 
Solution

JohnnyPicnic

New Member
Joined
Feb 8, 2021
Messages
5
Office Version
  1. 2019
Platform
  1. Windows
VBA Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,505
Messages
5,625,199
Members
416,080
Latest member
blemon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top