根据列中的条件删除行 [英] Delete rows based on condition in a column

问题描述

我在网上搜索，在这里搜索，但找不到符合账单和工作的东西。我有一些代码工作，但因为范围的长度变化，因为它删除行，它不捕获所有行删除，所以我最终运行它几次，这不是很好...所以我'我现在尝试按照这个线程推荐的AutoFilter方法。

我有一个包含几列的电子表格，其中一个是成本。我需要通过成本列（有时可能是第9列，有时为10，因此下面的查找位），并删除成本为0的任何行...

以下是我迄今为止写的代码，任何帮助将非常非常感谢!!!!

- 编辑3：一些更多的代码更改已经...新代码在下面。

  Sub RemoveRows（）
 Dim cell As范围
 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：= *，之后：=范围（A1），LookIn：= xlValues，LookAt：= xlPart，SearchOrder：= xlByColumns，SearchDirection：= xlPrevious）.Column 
 
行（1）.EntireRow。选择
 criteriaColumn = Selection.Find（What：=Cost，After：= ActiveCell ，LookIn：= _ 
 xlValues，LookAt：= xlPart，SearchOrder：= xlByRows，SearchDirection：= _ 
 xlNext，MatchCase：= False，SearchFormat：= False）.Column 
设置criteriaRange =范围（A1，Cells（lastRow，lastColumn））
 
 criteriaRange.AutoFilter字段：= criteriaColumn，Criteria1：=£0.00
 AutoFilter.Range.Delete 
 ActiveSheet .AutoFilterMode = False 
 End Sub 
  

当我运行它时，它将过滤器应用于正确的范围和正确的列。然后我收到错误消息：运行时错误424'：需要的对象，适用于 AutoFilter.Range.Delete 行。当我在此处按结束或调试并查看电子表格时，过滤器已应用于正确的列，并且选中了正确的选项（£0.00），但没有返回结果（我知道我需要做将来出现这种情况的一些错误处理，但现在应至少有10行返回此过滤器）。如果我在电子表格中的过滤器设置上手动按确定（不改变任何一个！），我的10个结果显示正确，所以我不知道为什么我们以编程方式显示它们不显示？

如果需要，很高兴提供电子表格示例，这真的让我感到困惑！

电子表格可以找到 here

解决方案

您应该可以使用Excel AutoFilter删除行。

  Cells.AutoFilter字段：= criteriaColumn，Criteria1：=£0.00
 ActiveSheet.AutoFilter.Range.Offset（1）.Delete 
 ActiveSheet.ShowAllData 
  

这将过滤cost = 0，然后删除与过滤器匹配的所有行。

操作员是自动等于所以您不需要=£0.00作为参数。

我在ActiveSheet中添加AutoFilter可以摆脱未找到的对象，而Offset则不会删除头文件。

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.

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...

Below is the code I've written so far, any help would be very, very much appreciated!!!!

-- edit 3: some more code changes have been made...new code is below.

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

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 AutoFilter.Range.Delete 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?

Happy to supply spreadsheet example if required, this is really confusing me!!

Spreadsheet can be found here

解决方案

You should be able to use the Excel AutoFilter to delete the rows.

Cells.AutoFilter Field:=criteriaColumn, Criteria1:="£0.00"
ActiveSheet.AutoFilter.Range.Offset(1).Delete
ActiveSheet.ShowAllData

This will filter for cost = 0 and then delete all the rows that match the filter.

The operater is automatically equals so you don't need "=£0.00" as the parameter.

I added in the ActiveSheet for AutoFilter to get rid of the object not found, and the Offset makes it not delete headers.

这篇关于根据列中的条件删除行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！