Results 1 to 4 of 4

Thread: Slow delete loop
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    May 2005
    Location
    Tokyo
    Posts
    46
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Slow delete loop

    Hi Experts,

    My loop deletes all rows, which do have the word table in the first column. However, this is extremely slow for worksheets with over 10000 rows.

    Does anybody has a faster coding idea?

    j = 1
    While Sheets(m).Cells(j, 1) <> ""
    If Sheets(m).Cells(j, 1) = "table" Then
    Rows(j & ":" & j).Select
    Selection.Delete Shift:=xlUp
    j = j - 1
    End If
    j = j + 1
    Wend

    Regards,

  2. #2
    MrExcel MVP
    Join Date
    Aug 2004
    Location
    Tokyo, Japan
    Posts
    16,995
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Less access to the object faster the process
    Code:
    Sub test()
    Dim i As Long, txt As String
    again:
    For Each r In Range("a1", Range("a65536").End(xlUp)).SpecialCells(xlCellTypeConstants)
        If r = "Table" Then txt = txt & r.Row & ":" & r.Row & ","
        If Len(txt) > 240 Then
            txt = Left(txt, Len(txt) - 1)
            Range(txt).Delete: txt = Empty: GoTo again
        End If
    Next
    If Len(txt) > 0 Then
        txt = Left(txt, Len(txt) - 1)
        Range(txt).Delete
    End If
    End Sub

  3. #3
    MrExcel MVP Richie(UK)'s Avatar
    Join Date
    May 2002
    Location
    UK
    Posts
    3,329
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi N,

    OK, as regards generally speeding-up code its worth remembering that the 2 main culprits for making code slow are often calculation and screenupdating. Therefore, you could try switching them off at the start of the routine and then reset again at the end.

    Like this:
    Code:
    Sub Faster()
        Dim lCalc As Long
        
        With Application
            lCalc = .Calculation
            .Calculation = xlCalculationManual
            .ScreenUpdating = False
        End With
        
        '
        'do slow stuff here
        '
        
        With Application
            .Calculation = lCalc
            .ScreenUpdating = True
        End With
        
    End Sub
    That said, the very act of looping can be a major cause of delay - you have to check all of the rows to check whether the condition is satisfied. There are alternatives such as using Find or the AutoFilter - have a Search as this has been covered many times before.

    HTH
    Richie

  4. #4
    New Member
    Join Date
    May 2005
    Location
    Tokyo
    Posts
    46
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks Jindon & Richie,

    geee, yes, this has been covered over and over. Thanks for the hint. I went with this solution:

    Sub del_table()

    Dim EvalRange As Range

    Set EvalRange = Range("A1:A" & Range("A65536").End(xlUp).Row)
    EvalRange.AutoFilter Field:=38, Criteria1:="table"
    EvalRange.Offset(1).Resize(EvalRange.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    EvalRange.AutoFilter

    End Sub

    Regards,

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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