none
MS Excel VBA Really really slow RRS feed

  • Question

  • I have this embedded in a do loop and it's ridiculously slow as I have 20k records.  The code goes through single records with multiple cells in the far right for different sets for the same record name and adds rows for copy records to be created below for each set.  An item could have 1 to infinite numbers of sets but I need to create an additional record for each set so each individual item has a different record. 

    Example

    Change this:

    Item // Details //  Set 1 // Set 2 // Set 3 // Set 4 // ..... // Set N

    to this:

    Item // Details //  Set 1

    Item // Details // Set 2

    Item // Details // Set 3

    Item // Details // Set 4

    Item // Details // .....

    Item // Details // Set N

    The code fills down Item// Details // after it is busy making all the additional rows, I have left its code out due for brevity.  Just looking for some way to speed this up as going through all 20k records is taking about 45 minutes.

    Private Sub AddRows(LngCurrRowCount, LngRowCount, LngCurrRow, LngCurrCount, LngCurrColumn) Dim strCurrCellValue As String If IsEmpty(ActiveCell.Offset(LngCurrRow, LngCurrColumn).Value) = False Then strCurrCellValue = Trim(ActiveCell.Offset(LngCurrRow, LngCurrColumn).Value) ActiveCell.Offset(LngCurrRow, LngCurrColumn).Value = vbNullString If UCase(strCurrCellValue) = "RESERVED" Then ActiveCell.Offset(LngCurrRow + 1, 19).Value = "R" LngCurrColumn = LngCurrColumn + 1 Else ActiveCell.Offset(LngCurrRow + 1, 0).EntireRow.Insert Shift:=xlDown, _ CopyOrigin:=xlFormatFromLeftOrAbove ActiveCell.Offset(LngCurrRow + 1, 6).Value = Trim(strCurrCellValue) LngCurrColumn = LngCurrColumn + 1 LngCurrCount = LngCurrCount + 1 End If Else LngCurrRow = LngCurrRow + 1 LngCurrColumn = 7 End If End Sub

    I run this at the beginning of the code to speed it up.

    Application.ScreenUpdating = False
        Application.DisplayStatusBar = False
        Application.Calculation = xlCalculationManual
        Application.EnableEvents = False
        ActiveSheet.DisplayPageBreaks = False

    Wednesday, December 23, 2015 9:35 PM

Answers

  • I can't relate your routine to what you describe as your objective. But inserting rows is indeed slow and compounded by anything like use of Select or reactivating ActiveCell. That's not VBA's fault but Excel's object model and would be the same if not slower in other languages.

    For what you describe try doing it in memory and dump the results, if necessary inserting blocks of rows if you need to insert between other stuff or make a new sheet. Maybe you can adapt the following -

    Sub test()
    Dim i As Long, k As Long
    Dim s As String
    Dim arrSplit, arrOut
    Const cSep As String = "//"
    
    Dim StringIn As String
        StringIn = "Item // Details // Set 1 // Set 2 // Set 3 // Set 4 // ..... // Set N"
    
        arrSplit = Split(StringIn, cSep)
        If UBound(arrSplit) > 2 Then
            s = arrSplit(0) & cSep & arrSplit(1) & cSep
            ReDim arrOut(1 To UBound(arrSplit) - 1, 1 To 1)
    
            For i = 2 To UBound(arrSplit)
                k = k + 1
                arrOut(k, 1) = s & arrSplit(i)
            Next
        End If
        Range("A2").Resize(UBound(arrOut)) = arrOut
    
    End Sub
    There was something odd about one of the separators in your example string, but corrected in this macro.


    Thursday, December 24, 2015 10:27 AM
    Moderator
  • Hi Myysterio,

    The Com operation through Excel object model is time-consuming. To improve the performance, we can set the values of range via array like Perter suggested.

    Another workaround may improve the Excel performance is using Open XML.

    Hope it is helpful.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, December 29, 2015 8:21 AM
    Moderator

All replies

  • I can't relate your routine to what you describe as your objective. But inserting rows is indeed slow and compounded by anything like use of Select or reactivating ActiveCell. That's not VBA's fault but Excel's object model and would be the same if not slower in other languages.

    For what you describe try doing it in memory and dump the results, if necessary inserting blocks of rows if you need to insert between other stuff or make a new sheet. Maybe you can adapt the following -

    Sub test()
    Dim i As Long, k As Long
    Dim s As String
    Dim arrSplit, arrOut
    Const cSep As String = "//"
    
    Dim StringIn As String
        StringIn = "Item // Details // Set 1 // Set 2 // Set 3 // Set 4 // ..... // Set N"
    
        arrSplit = Split(StringIn, cSep)
        If UBound(arrSplit) > 2 Then
            s = arrSplit(0) & cSep & arrSplit(1) & cSep
            ReDim arrOut(1 To UBound(arrSplit) - 1, 1 To 1)
    
            For i = 2 To UBound(arrSplit)
                k = k + 1
                arrOut(k, 1) = s & arrSplit(i)
            Next
        End If
        Range("A2").Resize(UBound(arrOut)) = arrOut
    
    End Sub
    There was something odd about one of the separators in your example string, but corrected in this macro.


    Thursday, December 24, 2015 10:27 AM
    Moderator
  • Item // Details // Set 1 // Set 2 // Set 3 // Set 4 // ..... // Set N

    This is supposed to represent cell values within a single record.  // represented a different column.  Not sure how this will impact that; however, I'll look at your example and see how that applies.  I gotta learn what some of these functions do.  I'm not particularly familiar with them as you can see from my original code.

    Monday, December 28, 2015 7:00 PM
  • Hi Myysterio,

    The Com operation through Excel object model is time-consuming. To improve the performance, we can set the values of range via array like Perter suggested.

    Another workaround may improve the Excel performance is using Open XML.

    Hope it is helpful.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, December 29, 2015 8:21 AM
    Moderator