19

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?

Community
  • 1
  • 1
mbigun
  • 1,304
  • 4
  • 19
  • 46

2 Answers2

44

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
Jon Crowell
  • 21,695
  • 14
  • 89
  • 110
  • Method explained on [this page](http://www.ozgrid.com/VBA/row-delete-criteria.htm) combines the filtering and offsetting and deleting, which makes reading code easier. `With rRange 'Filter, offset(to exclude headers) and delete visible rows .AutoFilter Field:=lCol, Criteria1:=strCriteria .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete End With` – Ethun_Hunt Mar 18 '16 at 10:36
  • just precede the delete line with a try-catch block as it throws an error if you try to delete an empty selection : On Error Resume Next – Shankar ARUL Aug 29 '16 at 11:00
  • Hi just wondering how can I modify the above line to delete hidden rows after filter ? – Stupid_Intern Nov 02 '16 at 15:30
  • What is `lines`? is it a vba **row** variable or does the programmer define it? – Timo Oct 27 '21 at 12:49
20

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

KeyLimePy
  • 301
  • 2
  • 4
  • Good solution, but works only with autofiltered views, not with data marked up as table... It would be just perfect, if it would do. – Evgeniy Nov 29 '19 at 09:53