VBA:如何在Excel中删除过滤的行? [英] VBA: How to delete filtered rows in Excel?
问题描述
ActiveSheet.Range( $ A $ 1 $ $ $&$)$。 ).AutoFilter字段:= 8,Criteria1:= _
=
ActiveSheet.Range($ A $ 1:$ I $& _
=
ActiveSheet.UsedRange.Offset(1,0).Resize(ActiveSheet.UsedRange.rows.Count - 1).rows.Delete
ActiveSheet.ShowAllData
只有在此列中有空白单元格时,它才起作用。
但是我遇到一个问题,当我没有空白单元格,并通过使用上面的代码,我的范围是从表单中删除。如何避免这个问题?我应该更改过滤器条件还是别的?
使用SpecialCells删除仅在autofiltering后可见的行: p>
ActiveSheet.Range($ A $ 1:$ I $& lines).SpecialCells _
(xlCellTypeVisible)。 EntireRow.Delete
如果您的范围中的标题行不想删除,添加一个偏移范围以排除它:
ActiveSheet.Range($ A $ 1:$ I $& lines ).Offset(1,0).SpecialCells _
(xlCellTypeVisible).EntireRow.Delete
I have an Excel table that contains some data. By using next vba code I'm trying to filter only blank cells in some fields and delete these rows
ActiveSheet.Range("$A$1:$I$" & lines).AutoFilter Field:=7, Criteria1:= _
"="
ActiveSheet.Range("$A$1:$I$" & lines).AutoFilter Field:=8, Criteria1:= _
"="
ActiveSheet.Range("$A$1:$I$" & lines).AutoFilter Field:=9, Criteria1:= _
"="
ActiveSheet.UsedRange.Offset(1, 0).Resize(ActiveSheet.UsedRange.rows.Count - 1).rows.Delete
ActiveSheet.ShowAllData
It works only if I have blank cells in this columns. But I faced with a problem, when I do not have blank cells, and by using above code all my range is removing from the sheet. How to avoid this issue? Should I change my filter condition or something else?
Use SpecialCells to delete only the rows that are visible after autofiltering:
ActiveSheet.Range("$A$1:$I$" & lines).SpecialCells _
(xlCellTypeVisible).EntireRow.Delete
If you have a header row in your range that you don't want to delete, add an offset to the range to exclude it:
ActiveSheet.Range("$A$1:$I$" & lines).Offset(1, 0).SpecialCells _
(xlCellTypeVisible).EntireRow.Delete
这篇关于VBA:如何在Excel中删除过滤的行?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!