title: Delete rows based on condition in a column
body: <p>I've searched online and on here but can't find anything which seems to fit the bill and work.  I have got some code which works but because the length of the range changes as it deletes rows it doesn't catch all of the rows to delete and so I end up running it several times which isn't great...so I'm now trying the AutoFilter approach as recommended on this thread.</p>
  
  <p>I have a spreadsheet with several columns, one of which is 'cost'.  I need to go through the 'cost' column (which can sometimes be column 9, sometimes 10, hence the 'find' bit below) and delete any row where the cost is 0...</p>
  
  <p>Below is the code I've written so far, any help would be very, very much appreciated!!!!</p>
  
  <p>-- edit 3: some more code changes have been made...new code is below.</p>
  
  <pre><code>Sub RemoveRows()
      Dim cell As Range
      Dim lastRow As Long
      Dim lastColumn As Integer
      Dim criteraColumn As Integer
      Dim criteriaRange As Range
  
      lastRow = Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row
      lastColumn = Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
  
      Rows(1).EntireRow.Select
      criteriaColumn = Selection.Find(What:="Cost", After:=ActiveCell, LookIn:= _
                          xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
                          xlNext, MatchCase:=False, SearchFormat:=False).Column
      Set criteriaRange = Range("A1", Cells(lastRow, lastColumn))
  
      criteriaRange.AutoFilter Field:=criteriaColumn, Criteria1:="£0.00"
      AutoFilter.Range.Delete
      ActiveSheet.AutoFilterMode = False
  End Sub
  </code></pre>
  
  <p>When I run this, it applies the filter on the correct range and to the correct column.  I then get the error message: "Run-time error '424': Object required" which applies to the <code>AutoFilter.Range.Delete</code> line.  When I press 'end' or 'debug' on this and look at the spreadsheet, the filter has been applied to the correct column and the correct option (£0.00) is checked, but no results are returned (I know I need to do some error handling in case of this situation in future but for now there should be at least 10 lines returned by this filter).  If I manually press 'OK' on the filter settings in the spreadsheet (without changing any of them!) my 10 results show up correctly so I'm not sure why they're not showing up when I do this programmatically?</p>
  
  <p>Happy to supply spreadsheet example if required, this is really confusing me!!</p>
  
  <p>Spreadsheet can be found <a href="http://dl.dropbox.com/u/87261581/test%20phone%20data.xls" rel="nofollow">here</a></p>