VBA How to delete filtered rows in Excel

Question

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    amp  lines  AutoFilter Field  7  Criteria1                 ActiveSheet Range   A 1  I    amp  lines  AutoFilter Field  8  Criteria1                 ActiveSheet Range   A 1  I    amp  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

User · Answer

As an alternative to using UsedRange or providing an explicit range address, the AutoFilter.Range property can also specify the affected range.

ActiveSheet.AutoFilter.Range.Offset(1,0).Rows.SpecialCells(xlCellTypeVisible).Delete(xlShiftUp)

As used here, Offset causes the first row after the AutoFilter range to also be deleted. In order to avoid that, I would try using .Resize() after .Offset().

User · Answer

Use SpecialCells to delete only the rows that are visible after autofiltering   ActiveSheet Range   A 1  I    amp  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    amp  lines  Offset 1  0  SpecialCells        xlCellTypeVisible  EntireRow Delete

[vba] VBA: How to delete filtered rows in Excel?

Examples related to vba

Examples related to excel

Examples related to excel-2007