Delete rows that are visible after applying filter

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

I have the following code:

Selection.AutoFilter Field:=4, Criteria1:="=*ÖVROBL*", Operator:=xlAnd

Now I would like to delete rows that are visible except the first which
contains
the headlines.

How do I write that?
Br
Sverre
 
Ok, works!

What I dont understand is the following part of the code:

With ActiveSheet.AutoFilter.Range
On Error Resume Next
Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng Is Nothing Then rng.EntireRow.delete

Does it tell Excel to move down in the visible area to check wheter the row
is nonempty and if that is the case-delete the row?

Many thanks
Sverker

"Ron de Bruin" skrev:
 
Hi sverre

It will delete all visible cells in rng
rng is the whole ActiveSheet.AutoFilter.Range without the header row
With ActiveSheet.AutoFilter.Range
On Error Resume Next
Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
Click to expand...
 
Hi

So if I would like to modify the column I filter in I just change the range
from A1:A100 to for example D1:100 ?? Or do I need to change any else in the
code?

Best regards
Sverre

"Ron de Bruin" skrev:
 
Only change this line

..Range("A1:A100").AutoFilter Field:=1, Criteria1:=DeleteValue

Note that A1 is the Header cell

So you can use

..Range("D1:D100").AutoFilter Field:=1, Criteria1:=DeleteValue
 
Back
Top