StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PODelete rows based on condition in a column
text
Body
copied!<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>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload