Forum Discussion
csoares
Oct 11, 2021Brass Contributor
How to standardize dates
Good Morning I need to make a macro to fix all the Date entries that are entered. Some dates are entered with 10/01/2021" others "01-10-2021" or "01.10.2021". With this routine, when opening the fi...
- Oct 11, 2021
This should be faster:
Sub Formatar_Datas() Dim m As Long Application.ScreenUpdating = False Application.EnableEvents = False Application.DisplayAlerts = False Application.Calculation = xlCalculationManual m = Folha3.Range("C" & Folha3.Rows.Count).End(xlUp).Row With Range("J4:J" & m & ",Q4:Q" & m & ",R4:R" & m & ",S4:S" & m & ",T4:T" & m & _ ",U4:U" & m & ",V4:V" & m & ",W4:W" & m & ",X4:X" & m & ",Y4:Y" & m & _ ",Z4:Z" & m & ",AA4:AA" & m & ",AG4:AG" & m & ",AI4:AI" & m & ",AK4:AK" & m & _ ",AT4:AT" & m & ",AV4:AV" & m & ",AW4:AW" & m & ",AZ4:AZ" & m & _ ",BB4:BB" & m & ",BD4:BD" & m & ",BM4:BM" & m & ",BO4:BO" & m) .Replace What:=".", Replacement:="/" .Replace What:="-", Replacement:="/" .Replace What:="_", Replacement:="/" End With 'Application.Calculation = xlCalculationautomatic Application.DisplayAlerts = True Application.EnableEvents = True Application.ScreenUpdating = True End Sub
But are all dates in ddmmyyyy order? Or all in mmddyyyy order? If there is a mixture, you have a problem...
HansVogelaar
Oct 11, 2021MVP
This should be faster:
Sub Formatar_Datas()
Dim m As Long
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual
m = Folha3.Range("C" & Folha3.Rows.Count).End(xlUp).Row
With Range("J4:J" & m & ",Q4:Q" & m & ",R4:R" & m & ",S4:S" & m & ",T4:T" & m & _
",U4:U" & m & ",V4:V" & m & ",W4:W" & m & ",X4:X" & m & ",Y4:Y" & m & _
",Z4:Z" & m & ",AA4:AA" & m & ",AG4:AG" & m & ",AI4:AI" & m & ",AK4:AK" & m & _
",AT4:AT" & m & ",AV4:AV" & m & ",AW4:AW" & m & ",AZ4:AZ" & m & _
",BB4:BB" & m & ",BD4:BD" & m & ",BM4:BM" & m & ",BO4:BO" & m)
.Replace What:=".", Replacement:="/"
.Replace What:="-", Replacement:="/"
.Replace What:="_", Replacement:="/"
End With
'Application.Calculation = xlCalculationautomatic
Application.DisplayAlerts = True
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
But are all dates in ddmmyyyy order? Or all in mmddyyyy order? If there is a mixture, you have a problem...
- csoaresOct 11, 2021Brass ContributorIt was almost instantaneous :-), I'll try to understand the changes and find out why it was super fast 🙂
Dates are all in dd/mm/yyyy format.
Thank you for your help- HansVogelaarOct 11, 2021MVP
The code is so fast because it does not loop through the cells. It uses the Replace method of the range to act on all cells in one go.
- csoaresOct 12, 2021Brass ContributorCan I use the same method as With to erase the contents of all cells, in a given column, that do not start with the characters "PT"?
I can do it with a cycle, but I'm not seeing how I can do it faster. Thanks