VBA to slow for large dataset - Please suggest some optimizations

[robertgama]

I have a sheet that will have between 50,000 to 80,000 rows.

I started writing a macro but it's very slow and I've only processed a few columns of data so far.

I'm thinking that perhaps instead of writing the values one at a time I should read them in one column at at time, or put formulas in the top cell, select the whole range down then copy the formula for each column? Maybe copy/paste values only after I process each column?

Any suggestions would be great. Here is a bit of the code. (aaRegexReplace & aaRemoveInBrackets are just a couple of UDF's that are quick enough). It's mainly the reading and writing that I imagine is taking up all the time.

Sub btnRegen_Click()
    ' Save the current state of Excel settings.
    screenUpdateState = Application.ScreenUpdating
    statusBarState = Application.DisplayStatusBar
    calcState = Application.Calculation
    eventsState = Application.EnableEvents
    displayPageBreakState = ActiveSheet.DisplayPageBreaks
    Application.ScreenUpdating = False
    Application.DisplayStatusBar = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
    ActiveSheet.DisplayPageBreaks = False
    




    Dim numRecords As Long
    Dim i As Long
    Dim curRow As Long
    Dim curCol As Integer
    Dim startRow As Integer
    Dim lastRow As Long
    Dim outputRow As Long
    Dim numColumns As Integer
    Dim ds As Worksheet
    
    Set ds = Worksheets(Range("dataSheetName").Value)
    
    numColumns = Range("numberOfColumns").Value
    numRecords = Range("numberOfRecords").Value
    startRow = Range("startRow").Value
    lastRow = Range("lastRow").Value


    For i = 1 To numRecords
        curRow = i + 1
        outputRow = startRow + i - 1
        
        Cells(outputRow, 1).Value = ds.Cells(curRow, 1).Value
        Cells(outputRow, 2).Value = "ADS_Client_" + CStr(Cells(outputRow, 1).Value)
        If Len(ds.Cells(curRow, 2).Value) > 70 Then
            Cells(outputRow, 3).Value = Left(aaRegexReplace(aaRemoveInBrackets(ds.Cells(curRow, 2).Value), "(.*)( formerly.*)", "\1"), 70)
        Else
            Cells(outputRow, 3).Value = ds.Cells(curRow, 2).Value
        End If
        Cells(outputRow, 4).Value = Cells(outputRow, 3).Value + " [FROM ADS]"
        Cells(outputRow, 5).Value = aaProvinceStateFix(ds.Cells(curRow, 8).Value)
        Cells(outputRow, 7).Value = aaCountryFix(ds.Cells(curRow, 7).Value)
    Next i
 
 
    
    ' Restore Excel settings to original state.
    Application.ScreenUpdating = screenUpdateState
    Application.DisplayStatusBar = statusBarState
    Application.Calculation = calcState
    Application.EnableEvents = eventsState
    ActiveSheet.DisplayPageBreaks = displayPageBreaksState
    


End Sub

Thanks,

Rob.

 

[wigi]

Hello Rob

The technique that saves time is working in RAM memory.
What costs time is the interaction with the sheet - read from and write to cells.
For simple code, see my previous contribution: http://www.mrexcel.com/forum/excel-questions/717375-pick-another-word.html

 

[robertgama]

Thanks for the reply.

I'll see if I can understand.

In your previous post:
http://www.mrexcel.com/forum/excel-questions/717375-pick-another-word.html#post3535414

You wrote:

Sub wigi()

    Dim i As Long
    
    Application.ScreenUpdating = False
    
    On Error Resume Next
    
    vWords = Array("margin", "standard class", "current Account (Vm)", "Current account (VI)")




    With Cells(1).CurrentRegion.Columns(2)
        sq = .Value
        For i = 2 To UBound(sq)
            If IsError(WorksheetFunction.Match(sq(i, 1), vWords, 0)) Then sq(i, 1) = ""
        Next
        .Value = sq
        .SpecialCells(4).EntireRow.Delete
    End With


End Sub

Are you suggesting that I iterate through my columns and read them all in one at a time with the following line?

With Cells(1).CurrentRegion.Columns(2)

Then it looks like you are iterating all of the values in the array and writing them back one at a time? Is there a way to write the whole array (once processed) back to a range all at once? Should I read them all in at once like you have, loop through and process them using my UDF's writing the results back to an array, and then finally write the array to the range?

Thanks again,

Robert.

 

[wigi]

You can read out 1 column at a time, or a range. The technique is similar, only that the range should be rectangular instead of a single column.

I read out 1 column and store it in the variable sq, then I loop through the values in sq and update sq (not the sheet !), then I write back to the sheet at once.
These 2 steps are exactly:

        sq = .Value        For i = 2 To UBound(sq)
            If IsError(WorksheetFunction.Match(sq(i, 1), vWords, 0)) Then sq(i, 1) = ""
        Next
        .Value = sq

 

[robertgama]

Ah yes, genius. I didn't notice the (.value = sq) was outside the loop.

Slick!

Thank you!