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

ElPabloSS

New Member
Joined
Sep 12, 2018
Messages
10
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,460
Office Version
365
Platform
Windows
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

New Member
Joined
Sep 12, 2018
Messages
10
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,460
Office Version
365
Platform
Windows
I combined one line, and suppressed calculations and screen updating until the macro is complete.
See if that improves things:
Code:
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

New Member
Joined
Sep 12, 2018
Messages
10
I combined one line, and suppressed calculations and screen updating until the macro is complete.
See if that improves things:
Code:
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,460
Office Version
365
Platform
Windows
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".
 

Forum statistics

Threads
1,081,845
Messages
5,361,665
Members
400,643
Latest member
RockStar89

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top