Results 1 to 13 of 13

Time to Execute Code when Deleting Rows

Threaded View

  1. #1
    Forum Contributor
    Join Date
    06-21-2010
    Location
    -
    MS-Off Ver
    Excel 2010
    Posts
    1,211

    Time to Execute Code when Deleting Rows

    I have this code which is designed look for two things, either a "-" in column C or a zero value in column F and delete the rows specified in each range and then create a new worksheet based on the value in cell C8. This code works really well, however the problem I have is that I need to apply it to around 1,800 rows to do the same job. When I do so it takes hours to run!

    Is there anything that can be changed to make the approach more efficient and speed the code execution up when applied to 180 different ranges? I have no understanding of what causes the code to take so long to run, is it because of the number of rows or for some other reason?

    It should be noted that the size of the ranges differs for these sections, between 35, 20, 15 and 10 rows and the For J runs all the way to For J = 1830 to 1820 Step -1 in the full code so these two sections of code are repeated 90 times!

    For J = 175 To 145 Step -1
       If .Cells(J, "F") = 0 Then .Rows(J).EntireRow.Delete
    Next J

    whereas the size of the range is always consistent for these sections

    If Cells(139, "C") = "-" Then _
          Cells(137, "C").Resize(8, 1).EntireRow.Delete Shift:=xlUp
    This is the structure of the code...

    Sub CreateSheets()
    Dim arrNames, c
    Dim J As Integer
    
    arrNames = Worksheets("Summary").Cells(1).CurrentRegion
    Application.ScreenUpdating = False
    Worksheets("Template").Visible = True
        For c = LBound(arrNames, 1) + 1 To UBound(arrNames, 1)
                If arrNames(c, 2) = "Complete" Then
                    arrNames(c, 2) = "Finished"
                    Worksheets("Template").Copy After:=Worksheets(Worksheets.Count)
                        With ActiveSheet
                            .Name = arrNames(c, 1)
                            .Cells(8, "C").Value = .Name
                        End With
                        
                        With ActiveSheet
                        
                            For J = 175 To 145 Step -1
                                If .Cells(J, "F") = 0 Then .Rows(J).EntireRow.Delete
                            Next J
                            
                                If Cells(139, "C") = "-" Then _
                                    Cells(137, "C").Resize(8, 1).EntireRow.Delete Shift:=xlUp 
                            
                            For J = 155 To 120 Step -1
                                If .Cells(J, "F") = 0 Then .Rows(J).EntireRow.Delete
                            Next J
                            
                                If Cells(114, "C") = "-" Then _
                                    Cells(112, "C").Resize(8, 1).EntireRow.Delete Shift:=xlUp
    
                            For J = 110 To 75 Step -1
                                If .Cells(J, "F") = 0 Then .Rows(J).EntireRow.Delete
                            Next J
                            
                                If Cells(69, "C") = "-" Then _
                                    Cells(67, "C").Resize(8, 1).EntireRow.Delete Shift:=xlUp
                        
                            For J = 63 To 29 Step -1 'FAR
                                If .Cells(J, "E") = 0 Then .Rows(J).EntireRow.Delete
                            Next J
                        
                        End With
                End If
        Next c
            With Worksheets("Summary")
                .Cells(1).CurrentRegion.Value = arrNames
                .Activate
                .Range("A1").Select
            End With
        Worksheets("Template").Visible = False
        Application.ScreenUpdating = True
        
    End Sub
    Many thanks
    Last edited by HangMan; 11-15-2015 at 06:13 PM.
    Thanks,
    HangMan

    You can say "Thank you!" by clicking Add Reputation below the post.
    Please, mark your thread [SOLVED] if you are happy with the solution.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. help with code that is taking forever to execute- Copying formula down, matching, deleting
    By gjwilson1216 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-18-2015, 09:40 PM
  2. code to execute a keyboard shortcut or to execute a toolbar button
    By Gti182 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-09-2015, 12:16 PM
  3. [SOLVED] VBA code required not to execute during certain date and time range
    By mchilapur in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-15-2015, 08:57 PM
  4. [SOLVED] Code to execute multiple tasks such as deleting rows, inserting sums, setting print area
    By kosherboy in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-03-2013, 04:18 PM
  5. efficiency recomendations, code taking long time to execute...
    By am_hawk in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-04-2013, 03:55 PM
  6. Changing code from deleting rows to cut/paste rows into another sheet and delete blank row
    By kmarshall6576 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-18-2013, 01:54 AM
  7. Run a stopwatch for time then execute code
    By JFamilo in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-03-2005, 10:05 PM

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