CODEWITHSUNDEEP
excelvbadatefor-loopselection
Xavi
Xavi

Reputation: 207

Looking for improving the speed of a macro working with a loop and converting date to other format

My code below works but it is very slow… This code in fact consists to convert the date in column C and D of my sheet (called "Test") from format day.month.year to format day/month/year (For example please see the picture below, the lines 1-2-3-4-5 have been already converted whereas the other lines from line 1183 have not been converted yet).

I am looking for a solution to improve the speed of this macro because if I have a lot of lines to convert in column C and D, the macro is really really slow…

If by chance someone know how to improve the speed of this macro, that would be really fantastic.

enter image description here

Sub convertdatrighteuropeanformat()
    Dim cell As Range
    Call selectallmylinesctrlshiftdown

    Application.ScreenUpdating = False

    For Each cell In Selection
        With cell
            .NumberFormat = "@"
            .Value = Format(.Value, "dd/mm/yyyy")
        End With
    Next cell

    Selection.Replace What:="/", Replacement:=".", LookAt:=xlPart, _
       SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
       ReplaceFormat:=False

    Application.ScreenUpdating = True
End Sub


Sub selectallmylinesctrlshiftdown()
    With Sheets("Test")
        .Range(.Range("D2"), .Range("E2").End(xlDown)).Select
    End With
End Sub

Upvotes: 0

Views: 63

Answers (1)

urdearboy
urdearboy

Reputation: 14590

Instead of a loop, refer to the entire Range (previously Selection) at once inside the With block. This is combined into one sub, although there is nothing wrong with your decision to declare the range with a stand alone procedure.

Option Explicit

Sub convert()

Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Test")
Dim LRow As Long, MyCell As Range, MyRange As Range

LRow = ws.Range("D" & ws.Rows.Count).End(xlUp).Row
Set MyRange = ws.Range("D2:E" & LRow)

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
    With MyRange
        .Value = Format(.Value, "dd/mm/yyyy")
        .Replace "/", ".", xlPart, xlByRows
        .NumberFormat = "@"
    End With
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

Upvotes: 1

Related Questions