title: Most efficient way to delete row with VBA
body: <p>I currently have a macro that I use to delete a record if the ID doesn't exist in a list of ID's I created from an XML document. It does work like I want it to, however I have over 1000 columns in the spreadsheet (one for each day of the year until end of 2015) so it takes ages to delete the row and it can only do 1 or 2 before it says "Excel ran out of resources and had to stop". Below is the code I'm using for the macro, is there another way I can do this so that Excel doesn't run of of resources?</p>
  
  <pre><code>Sub deleteTasks()
  
  Application.ScreenUpdating = False
  
  Dim search As String
  Dim sheet As Worksheet
  Dim cell As Range, col As Range
  Set sheet = Worksheets("misc")
  Set col = sheet.Columns(4)
  
  ActiveWorkbook.Sheets("Schedule").Activate
  ActiveSheet.Range("A4").Select
  ActiveSheet.Unprotect
  ActiveSheet.Range("A:C").EntireColumn.Hidden = False
  
  Do While ActiveCell.Value &lt;&gt; ""
  
      search = ActiveCell.Value
  
      Set cell = col.Find(What:=search, LookIn:=xlValues, _
                   LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                   MatchCase:=False, SearchFormat:=False)
  
      If cell Is Nothing Then 'If the taskID is not in the XML list
  
      Debug.Print "Deleted Task: " &amp; ActiveCell.Value
      Selection.EntireRow.Delete
  
      End If
  
      ActiveCell.Offset(1, 0).Select 'Select next task ID
  
  Loop
  
  ActiveSheet.Range("A:B").EntireColumn.Hidden = True
  ActiveSheet.Protect
  End Sub
  </code></pre>
  
  <p>After trying lots of different options, including all the answers listed below. I have realized that whatever the method is, deleting a row with ~1100 columns is going to take a while on my average laptop (2.20 Ghz, 4GB RAM). Since the majority of the rows are empty I have found alternative method which is a lot faster. I just clear the cells which contain data (A:S) and then resize the table to remove the row where I just deleted the data from. This end result is exactly the same as <code>entireColumn.Delete</code>. Below is the code I'm using now</p>
  
  <pre><code>'New method - takes about 10 seconds on my laptop
  Set ws = Worksheets("Schedule")
  Set table = ws.ListObjects(1)
  Set r = ws.Range("A280:S280")
  
  r.Clear
  
  table.Resize Range("A3:VZ279")
  </code></pre>
  
  <p>Using anything involving <code>EntireColumn.Delete</code> or just manually selecting the row and deleting it takes about 20-30 seconds on my laptop. Of course this method only works if your data is in a table.</p>