Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Deleting Rows (2003)

    I am having trouble deleting rows in Excel. I try deleting rows manually and it takes at least 30 seconds or more to delete one row. I don't think deleting rows has been a problem before. I even have the following macro that I just got that worked last week but now it bogs down the system and takes forever to run - I cancel it because it is taking too long. The report I am trying to delete from only has 3500 rows. We had to spring ahead (time change) early and I did find some errors in Excel but I thought they weren't significant. Could a time change mess up some things in Excel? Here is the macro:
    Public Sub DeleteSuppOnly()
    Call DeleteByWord("Supplemental Only")

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Deleting Rows (2003)

    Switching to Daylight Saving Time shouldn't have any influence on your macro. You can try turning off screen updating and automatic recalculation temporarily:

    Sub DeleteByWord(ByVal strWord As String)
    Dim wks As Worksheet
    Dim rngToSearch As Range
    Dim rngFound As Range
    Dim rngFoundAll As Range
    Dim strFirstAddress As String

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Set wks = Sheets("Main Report")
    Set rngToSearch = wks.Columns("P")
    Set rngFound = rngToSearch.Find(What:=strWord, _
    Lookat:=xlWhole, _
    LookIn:=xlFormulas, _
    MatchCase:=False)
    If Not rngFound Is Nothing Then
    Set rngFoundAll = rngFound
    strFirstAddress = rngFound.Address
    Do
    Set rngFoundAll = Union(rngFound, rngFoundAll)
    Set rngFound = rngToSearch.FindNext(rngFound)
    Loop Until rngFound.Address = strFirstAddress
    rngFoundAll.EntireRow.Delete
    End If
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    End Sub

    Note: if you cancel the macro halfway through, calculation will still be set to manual, so you'll have to set it to automatic again (in the Calculation tab of Tools | Options...)

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Deleting Rows (2003)

    An alternative version:

    Sub DeleteByWord(ByVal strWord As String)
    Dim wks As Worksheet
    Dim lngRow As Long
    Dim lngMaxRow As Long

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Set wks = Sheets("Main Report")
    lngMaxRow = wks.Range("P65536").End(xlUp).Row
    For lngRow = lngMaxRow To 1 Step -1
    If wks.Range("P" & lngRow) = strWord Then
    wks.Range("P" & lngRow).EntireRow.Delete
    End If
    Next lngRow
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    End Sub

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting Rows (2003)

    I want to delete the row that has "Supplemental Only" in the column. I don't see anything in the code that designates this. Sorry - I figured it out. It works much faster. Thanks

Posting Permissions

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