Results 1 to 33 of 33

Why is macro so effing slow???

Threaded View

  1. #1
    Registered User
    Join Date
    05-27-2024
    Location
    southeast Michigan
    MS-Off Ver
    2010
    Posts
    29

    Question Why is macro so effing slow???

    Here is the macro I wrote:

    Sub RemoveNotAddRows()
        Dim bDelRow As Boolean
        Dim c, r, lastRow As Long
        Dim checkVals(30) As Integer
        Dim begSumCol, endSumCol As Integer
        Dim strTmp As String
        Dim wb As Workbook
        Dim ws As Worksheet
    
        With Application
            .ScreenUpdating = False
            .Calculation = xlManual
        End With
    
        Set wb = ActiveWorkbook
        Set ws = wb.Sheets("Sheet1")
        
        begSumCol = -99
        endSumCol = -99
        
        For c = 1 To 30
            strTmp = Left(ws.Cells(1, c), 4)
            If strTmp <> "Cell" Then
                If begSumCol = -99 Then
                    begSumCol = c
                ElseIf Len(strTmp) = 0 Then
                    endSumCol = c - 1
                    Exit For
                End If
            End If
        Next c
    
        For c = begSumCol To endSumCol
            checkVals(c) = CInt(ws.Cells(1, c))
        Next c
        
        With ws
            lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        End With
        
        For r = lastRow To 2 Step -1
            bDelRow = False
            For c = begSumCol To endSumCol
                If CInt(ws.Cells(r, c)) <> checkVals(c) Then
                    bDelRow = True
                    Exit For
                End If
            Next c
            If bDelRow Then
                Rows(r).EntireRow.Delete
            End If
        Next r
    
        With Application
            .ScreenUpdating = True
            .Calculation = xlAutomatic
        End With
    End Sub
    Nothing special or fancy. Here's the issue: When I tried running this on a file with 43,352 rows and 7 columns of sums to match, I had to kill Excel after 10+ minutes because there were no results. However, when I manually checked the sums in two columns, and reduced the number of rows to 2170, the macro finished in 10 seconds. Simple match suggest that the original file should have finished in about three and one half minutes, but that didn't happen. Why?

    update: I just ran this macro again on a different file with 'only' 12,046 rows. Based on the above math, this should have finished in under one minute, but it dd not finish until almost five minutes had elapsed.
    Last edited by groston; 09-12-2024 at 03:22 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Slow macro, slow pc or wrong code?
    By corky81 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-24-2018, 05:58 AM
  2. slow macro
    By max_max in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 02-21-2016, 09:53 AM
  3. macro slow
    By max_max in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-09-2015, 02:56 PM
  4. Macro is running real slow and makes navigating the worksheet really slow after execution.
    By MichWolverines in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-03-2013, 04:29 PM
  5. [SOLVED] slow computer (slow clipboard) breaks my macro
    By twilsonco in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-05-2013, 09:16 PM
  6. Slow Macro
    By northbank in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 03-15-2010, 09:30 PM
  7. [SOLVED] Slow macro
    By alf bryn in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-04-2005, 08:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1