EXCEL VBA reformat and change column values in a time efficient way

[ElPabloSS]

Hi Team,

I really hope you can help me to fix my problem.
I have a huge(over 1000 rows) xls-sheet report containing a column "T" having date information (i call it information because the column is formatted as "general") in the US format MM/DD/YY. In order to use the date information, I need to convert the column "T" into date format and change the date format to "DD.MM.YYYY". So far, so good.
At the moment my solution is:

For Each cell In Rng​

Current_Date = CDate(Format$(cell.Value, "mm.dd.yyyy"))​

cell.Value = Format(Current_Date, "dd.mm.yyyy")​

cell.Value = DateValue(cell.Value)​

Next cell​

This solution works fine, but it takes about 5mins for the amount of rows in my report.
I was wondering if there is any more efficient way to get the same result. I played around with functions like:

Columns("T:T").Replace What:="/", Replacement:=".", LookAt:=xlPart​

but this function doesn't give me the option to switch from MM/DD/YY to DD.MM.YYYY.

Hope my question is understandable and you can help

thanks a lot
Stefan

 

[Joe4]

Welcome to the Board!

Over 1000 rows is really not that much, and it shouldn't take over 5 minutes to run, unless you are running it on a very slow computer, or you have other stuff (i.e. VBA code) going on here.
Can you post the entirety of your VBA procedure?
Do you have any other VBA code in the workbook, especially any event procedure code?

 

[ElPabloSS]

Welcome to the Board!

Over 1000 rows is really not that much, and it shouldn't take over 5 minutes to run, unless you are running it on a very slow computer, or you have other stuff (i.e. VBA code) going on here.
Can you post the entirety of your VBA procedure?
Do you have any other VBA code in the workbook, especially any event procedure code?

Hi Joe,
thanks for your answer.
Below is the entire macro (it runs on a laptop Lenovo T460, 2 years old but not slow at all AND no, I have no other procedures running in parallel):
The entire macro is a bit different to my example above and I need to change two columns in total, but the problem or question is still the same - is there another approach to reach same result/faster approach.

Public Sub reformatSheet()​

changeDateFormatColumn ("T")​

End Sub​

​

Private Sub changeDateFormatColumn(col As String)​

​

Dim cell As Range​

Dim Current_Date As Date​

Dim Date_String As String​

Dim i As Long​

​

lastRow = ActiveSheet.Range(col & Rows.Count).End(xlUp).Row​

Set Rng = Range(col & "2:" & col & lastRow)​

i = 2​

​

For Each cell In Rng​

Date_String = cell.Value​

Current_Date = CDate(Format$(Date_String, "mm/dd/yyyy"))​

cell.Value = Format(Current_Date, "dd.mm.yyyy")​

cell.Value = DateValue(cell.Value)​

​

Date_String = ActiveSheet.Range("U" & i).Value​

Current_Date = CDate(Format$(Date_String, "mm/dd/yyyy"))​

ActiveSheet.Range("U" & i).Value = Format(Current_Date, "dd.mm.yyyy")​

ActiveSheet.Range("U" & i).Value = DateValue(ActiveSheet.Range("U" & i).Value)​

i = i + 1​

Next cell​

End Sub​

Thank you,
Stefan

 

[Joe4]

I combined one line, and suppressed calculations and screen updating until the macro is complete.
See if that improves things:

Private Sub changeDateFormatColumn(col As String)

    Dim cell As Range
    Dim Current_Date As Date
    Dim Date_String As String
    Dim i As Long

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    lastRow = ActiveSheet.Range(col & Rows.Count).End(xlUp).Row
    Set Rng = Range(col & "2:" & col & lastRow)
    i = 2
    For Each cell In Rng
        Date_String = cell.Value
        Current_Date = CDate(Format$(Date_String, "mm/dd/yyyy"))
        cell.Value = DateValue(Format(Current_Date, "dd.mm.yyyy"))
        Date_String = ActiveSheet.Range("U" & i).Value
        Current_Date = CDate(Format$(Date_String, "mm/dd/yyyy"))
        ActiveSheet.Range("U" & i).Value = Format(Current_Date, "dd.mm.yyyy")
        ActiveSheet.Range("U" & i).Value = DateValue(ActiveSheet.Range("U" & i).Value)
        i = i + 1
    Next cell
    
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    
End Sub

 

[ElPabloSS]

I combined one line, and suppressed calculations and screen updating until the macro is complete.
See if that improves things:

Private Sub changeDateFormatColumn(col As String)

    Dim cell As Range
    Dim Current_Date As Date
    Dim Date_String As String
    Dim i As Long

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    lastRow = ActiveSheet.Range(col & Rows.Count).End(xlUp).Row
    Set Rng = Range(col & "2:" & col & lastRow)
    i = 2
    For Each cell In Rng
        Date_String = cell.Value
        Current_Date = CDate(Format$(Date_String, "mm/dd/yyyy"))
        cell.Value = DateValue(Format(Current_Date, "dd.mm.yyyy"))
        Date_String = ActiveSheet.Range("U" & i).Value
        Current_Date = CDate(Format$(Date_String, "mm/dd/yyyy"))
        ActiveSheet.Range("U" & i).Value = Format(Current_Date, "dd.mm.yyyy")
        ActiveSheet.Range("U" & i).Value = DateValue(ActiveSheet.Range("U" & i).Value)
        i = i + 1
    Next cell
    
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    
End Sub

Hi Joe,
what can I say... thank you so much, the problem is fixed.
The combination of that one line is a nice and good enhancement, but I think the game changer was the setting for "application.screenupdating" and "application.calculation".

Again, thank you!
Stefan

 

[Joe4]

You are welcome.

The combination of that one line is a nice and good enhancement, but I think the game changer was the setting for "application.screenupdating" and "application.calculation".

You are absolutely correct. That is where you get the most "bang for your buck".