CODEWITHSUNDEEP
vbaexcel
Motivated
Motivated

Reputation: 163

Extremely slow VBA code when formatting cells

When attempting to format 2 columns in a workbook, the execution of the macro is extremely slow. To format approximately 4000 rows, it takes over 10 minutes.

The dates are populated from an external source that stores them as strings.

When commenting the code, it loads under 60 seconds.

The code

'Discover last row of data
    RowsToProcess = Range("A" & Rows.Count).End(xlUp).Row

    For i = 6 To RowsToProcess
    Worksheets("Data").Range("B" & i).Select
    Selection.NumberFormat = "dd/mm/yy;;"
    Selection.Value = CDate(Selection.Value)

    Worksheets("Data").Range("C" & i).Select
    Selection.NumberFormat = "dd/mm/yy;;"
    Selection.Value = CDate(Selection.Value)

    Next i

The code below does not format cells in the required format either.

Worksheets("Data).Columns("C").NumberFormat = dd/mm/yy;;"

Upvotes: 1

Views: 2902

Answers (1)

Dan Wagner
Dan Wagner

Reputation: 2713

The post @aelgoa linked to is spot on. When the standard Application.ScreenUpdating options for speeding up your code aren't enough, I turn to Variant arrays.

(If you wanted to see how I use Application.ScreenUpdating etc., wrapped in a GoFast function, check out my answer here: VBA code optimization)

The script below works like this:

  1. Load the Range defined in columns B and C into a Variant array
  2. Apply CDate logic there (rather than accessing the Sheet every time)
  3. Write the CDate-modified array out to Sheet

One caveat though -- my question in the comment above about differentiating between mm/dd and dd/mm (say May 6th, 2014 vs June 5th, 2014) still stands. I'll modify the code below based on your thoughts there. Thanks! 

Option Explicit
Sub ProcessDates()

Dim AryColBandC As Variant
Dim DateFormatB As Date, DateFormatC As Date
Dim RngColBandC As Range
Dim LastRow As Long, Counter As Long
Dim MySheet As Worksheet

'set references up-front
Set MySheet = ThisWorkbook.Worksheets("Sheet1")
With MySheet
    LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
    Set RngColBandC = .Range(.Cells(6, 2), .Cells(LastRow, 3))
End With

'load the B-C column range into a variant array
AryColBandC = RngColBandC

'loop through the variant array, applying the date
'conversion to each entry in the array and writing back
For Counter = LBound(AryColBandC) To UBound(AryColBandC)
    DateFormatB = CDate(AryColBandC(Counter, 1)) '<~ temporarily store
    DateFormatC = CDate(AryColBandC(Counter, 2)) '<~ dates here

    AryColBandC(Counter, 1) = DateFormatB
    AryColBandC(Counter, 2) = DateFormatC
Next Counter

'write the results out to the sheet
For Counter = LBound(AryColBandC) To UBound(AryColBandC)
    MySheet.Cells(5 + Counter, 2) = AryColBandC(Counter, 1)
    MySheet.Cells(5 + Counter, 3) = AryColBandC(Counter, 2)
Next Counter

End Sub

Upvotes: 1

Related Questions