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$" & 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?

Hey, can you help me define what is the "lines" you mention in your code — Thang Dang
– Thang Dang, Commented Sep 26, 2021 at 19:51

Jon Crowell · Accepted Answer · 2013-06-19 15:24:52Z

45

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

edited Jun 19, 2013 at 15:24

answered Jun 19, 2013 at 15:15

Jon Crowell

22.4k15 gold badges94 silver badges113 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Ethun_Hunt Over a year ago

Method explained on this page 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

Shankar ARUL Over a year ago

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

Stupid_Intern Over a year ago

Hi just wondering how can I modify the above line to delete hidden rows after filter ?

Timo Over a year ago

What is lines? is it a vba row variable or does the programmer define it?

FJones Mar 14 at 7:14

I believe ,EntireRow.Delete may fail if something is not visible, e.g. hidden column or hidden sheet. .Rows.Delete seems to work. Some people use .Select prior to .EntireRow.Delete can solve some problems but has a similar problem with visibility. See here: answers.microsoft.com/en-us/msoffice/forum/all/…

KeyLimePy · Accepted Answer · 2016-04-18 17:28:19Z

21

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

answered Apr 18, 2016 at 17:28

KeyLimePy

3112 silver badges4 bronze badges

1 Comment

Evgeniy Over a year ago

Good solution, but works only with autofiltered views, not with data marked up as table... It would be just perfect, if it would do.

Collectives™ on Stack Overflow

VBA: How to delete filtered rows in Excel?

2 Answers 2

5 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related