Delete rows that are visible after applying filter
 

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

Delete rows that are visible after applying filter
 

See
http://www.rondebruin.nl/delete.htm

Try
http://www.rondebruin.nl/delete.htm#AutoFilter


--
Regards Ron de Bruin
http://www.rondebruin.nl



"sverre" wrote in message ...
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

Delete rows that are visible after applying filter
 

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:

See
http://www.rondebruin.nl/delete.htm

Try
http://www.rondebruin.nl/delete.htm#AutoFilter


--
Regards Ron de Bruin
http://www.rondebruin.nl



"sverre" wrote in message ...
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

Delete rows that are visible after applying filter
 

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


--
Regards Ron de Bruin
http://www.rondebruin.nl



"sverre" wrote in message ...
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:

See
http://www.rondebruin.nl/delete.htm

Try
http://www.rondebruin.nl/delete.htm#AutoFilter


--
Regards Ron de Bruin
http://www.rondebruin.nl



"sverre" wrote in message ...
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

Delete rows that are visible after applying filter
 

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:

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


--
Regards Ron de Bruin
http://www.rondebruin.nl



"sverre" wrote in message ...
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:

See
http://www.rondebruin.nl/delete.htm

Try
http://www.rondebruin.nl/delete.htm#AutoFilter


--
Regards Ron de Bruin
http://www.rondebruin.nl



"sverre" wrote in message ...
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

Delete rows that are visible after applying filter
 

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

--
Regards Ron de Bruin
http://www.rondebruin.nl



"sverre" wrote in message ...
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:

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


--
Regards Ron de Bruin
http://www.rondebruin.nl



"sverre" wrote in message ...
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:

See
http://www.rondebruin.nl/delete.htm

Try
http://www.rondebruin.nl/delete.htm#AutoFilter


--
Regards Ron de Bruin
http://www.rondebruin.nl



"sverre" wrote in message ...
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