| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| delete only visible rows in filter mode | Excel Discussion (Misc queries) | |||
| Help with filter Range & delete rows | Excel Programming | |||
| No. of rows selected from auto filter not visible on toolbar? | Excel Discussion (Misc queries) | |||
| Applying Formulas to Visible Cells Only | Excel Discussion (Misc queries) | |||
| Applying auto-filter with large number of rows | Excel Discussion (Misc queries) | |||
Linear Mode
