How to convert date format values of a Column into only numbers in same Column in excel with VBA-CodePudding

I want to convert dates values of a column into just numbers , the results should be in the same column,

I tried to convert the cells to a text format then remove the '/' character with this code :

    Sub removechar()
 Dim input1 As String 
 Dim result As String
 input1 = range("H1:H")
 Range("H1:H").NumberFormat = "@"
 result = Replace(input1, "/", "")
 Range("H1:H") = result
End Sub

the code worked with one cell but didn't with all the cells of the column. the desired result is attached in the image below the wanted result

CodePudding user response：

For the below discussion, I have assumed that the values are "real dates" with formatting to be displayed as you show.

Also, the destination column is formatted as General or as Number with no decimal places or separators.

If that is not the case, you can add that manually.

You can do this with a simple formula:

=--TEXT(H1,"ddmmyyy")

The double unary transforms the text string into a number

If you must use VBA, adjust the code below to fit with your actual source and destination requirements:

Option Explicit
Sub dateToNumber()
    Dim rSrc As Range
    Dim vSrc As Variant
    Dim I As Long
    
With ThisWorkbook.Worksheets("sheet13")
    Set rSrc = Range(.Cells(1, "H"), .Cells(.Rows.Count, "H").End(xlUp))
End With

'Faster to work in arrays than back and forth to the worksheet
vSrc = rSrc
For I = 1 To UBound(vSrc, 1)
    vSrc(I, 1) = Val(Format(vSrc(I, 1), "ddmmyyyy"))
Next I

'if you want to overwrite, remove Offset property from below line
With rSrc.Offset(columnoffset:=1)
    .EntireColumn.Clear
    .NumberFormat = "General"
    .Value = vSrc
    .EntireColumn.AutoFit
End With
    
End Sub