Add and Delete Excel Columns VBScript

[unique12u]

I have an excel sheet where column C is a date, Column D is a time and Column E is blank. Number of rows can vary.

I need code to tell it to add column C and D together into column E and then format column E as mm/dd/yyyy hh:mm:ss
and then delete column C and D.

Any help out there?

Thanks
Nique

 

[sahmiele]

This will get r done!




rowcounter = ActiveSheet.UsedRange.Rows.Count
For a = 2 To rowcounter
daterange = ("c" & a & ":c" & a)
timerange = ("d" & a & ":d" & a)
newcellrange = ("e" & a & ":e" & a)
Range(daterange).Select
datecell = ActiveCell.Text
Range(timerange).Select
timecell = ActiveCell.Text
newcellvalue = datecell & " " & timecell
Range(newcellrange).Select
ActiveCell.FormulaR1C1 = newcellvalue

Next
Columns("C").Select
Selection.Delete Shift:=xlToLeft

 

[unique12u]

How do I get it reformatted into my correct format?

mm/dd/yyyy hh:mm:ss

If I do the concatenation you suggest it will be m/d/yy hh:mm

 

[sahmiele]

Columns("c:c").Select
Selection.NumberFormat = "mm/dd/yyyy"
Columns("d:d").Select
Selection.NumberFormat = "[$-409]h:mm:ss AM/PM;@"
rowcounter = ActiveSheet.UsedRange.Rows.Count
For a = 2 To rowcounter
daterange = ("c" & a & ":c" & a)
timerange = ("d" & a & ":d" & a)
newcellrange = ("e" & a & ":e" & a)
Range(daterange).Select
datecell = ActiveCell.Text
Range(timerange).Select
timecell = ActiveCell.Text
newcellvalue = datecell & " " & timecell
Range(newcellrange).Select
ActiveCell.FormulaR1C1 = newcellvalue

Next
Columns("C").Select
Selection.Delete Shift:=xlToLeft