Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofilter & delete lines
I have recorded a macro which autofilters data and then deletes the lines
selected. The autofilter criteria are hard coded into the macro (e.g filter all items with description "Interim"). My problem is where there is no data with the the hard coded description in the particular batch of information I am running the macro on. Selection.AutoFilter Field:=45, Criteria1:="Interim" Range("AS3").Select ' This is the heading row in the column being filtered ActiveCell.Offset(1, 0).Select Range(Selection, Selection.End(xlDown)).Select Selection.SpecialCells(xlCellTypeVisible).Select Selection.EntireRow.Delete How do I get the macro to skip lines 2 to 6 above if there is no data with a description of Interim and to move onto the next hard coded description? Thanks in advance Craig |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofilter & delete lines
Craig,
Try On Error Resume Next With Range(Range("AS3"), Range("AS3").End(xlDown)) .AutoFilter Field:=1, Criteria1:="Interim" .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delet e .AutoFilter End With HTH, Bernie MS Excel MVP "Craig" wrote in message ... I have recorded a macro which autofilters data and then deletes the lines selected. The autofilter criteria are hard coded into the macro (e.g filter all items with description "Interim"). My problem is where there is no data with the the hard coded description in the particular batch of information I am running the macro on. Selection.AutoFilter Field:=45, Criteria1:="Interim" Range("AS3").Select ' This is the heading row in the column being filtered ActiveCell.Offset(1, 0).Select Range(Selection, Selection.End(xlDown)).Select Selection.SpecialCells(xlCellTypeVisible).Select Selection.EntireRow.Delete How do I get the macro to skip lines 2 to 6 above if there is no data with a description of Interim and to move onto the next hard coded description? Thanks in advance Craig |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofilter & delete lines
Hi Graig
See this example http://www.contextures.com/xlautofilter03.html#Copy (If you need help to change it post back) -- Regards Ron de Bruin http://www.rondebruin.nl "Craig" wrote in message ... I have recorded a macro which autofilters data and then deletes the lines selected. The autofilter criteria are hard coded into the macro (e.g filter all items with description "Interim"). My problem is where there is no data with the the hard coded description in the particular batch of information I am running the macro on. Selection.AutoFilter Field:=45, Criteria1:="Interim" Range("AS3").Select ' This is the heading row in the column being filtered ActiveCell.Offset(1, 0).Select Range(Selection, Selection.End(xlDown)).Select Selection.SpecialCells(xlCellTypeVisible).Select Selection.EntireRow.Delete How do I get the macro to skip lines 2 to 6 above if there is no data with a description of Interim and to move onto the next hard coded description? Thanks in advance Craig |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofilter & delete lines
Dim rng1 as Range, rng2 as Range
Dim rng as Range Selection.AutoFilter Field:=45, Criteria1:="Interim" set rng1 = Activesheet.autofilter.Range set rng2 = rng1.offset(1,0).Resize(rng1.rows.count - 1) set rng = rng2.columns(45) if application.Subtotal(3,rng) 0 then rng2.Delete End if -- Regards, Tom Ogilvy "Craig" wrote in message ... I have recorded a macro which autofilters data and then deletes the lines selected. The autofilter criteria are hard coded into the macro (e.g filter all items with description "Interim"). My problem is where there is no data with the the hard coded description in the particular batch of information I am running the macro on. Selection.AutoFilter Field:=45, Criteria1:="Interim" Range("AS3").Select ' This is the heading row in the column being filtered ActiveCell.Offset(1, 0).Select Range(Selection, Selection.End(xlDown)).Select Selection.SpecialCells(xlCellTypeVisible).Select Selection.EntireRow.Delete How do I get the macro to skip lines 2 to 6 above if there is no data with a description of Interim and to move onto the next hard coded description? Thanks in advance Craig |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofilter & delete lines
Craig,
Actually, you don't need the On Error Resume Next: the offset ensures that at least one row is visible - the row immediately below your data table. HTH, Bernie MS Excel MVP "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Craig, Try On Error Resume Next With Range(Range("AS3"), Range("AS3").End(xlDown)) .AutoFilter Field:=1, Criteria1:="Interim" .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delet e .AutoFilter End With HTH, Bernie MS Excel MVP "Craig" wrote in message ... I have recorded a macro which autofilters data and then deletes the lines selected. The autofilter criteria are hard coded into the macro (e.g filter all items with description "Interim"). My problem is where there is no data with the the hard coded description in the particular batch of information I am running the macro on. Selection.AutoFilter Field:=45, Criteria1:="Interim" Range("AS3").Select ' This is the heading row in the column being filtered ActiveCell.Offset(1, 0).Select Range(Selection, Selection.End(xlDown)).Select Selection.SpecialCells(xlCellTypeVisible).Select Selection.EntireRow.Delete How do I get the macro to skip lines 2 to 6 above if there is no data with a description of Interim and to move onto the next hard coded description? Thanks in advance Craig |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofilter & delete lines
I have a example here
http://www.rondebruin.nl/delete.htm#AutoFilter -- Regards Ron de Bruin http://www.rondebruin.nl "Ron de Bruin" wrote in message ... Hi Graig See this example http://www.contextures.com/xlautofilter03.html#Copy (If you need help to change it post back) -- Regards Ron de Bruin http://www.rondebruin.nl "Craig" wrote in message ... I have recorded a macro which autofilters data and then deletes the lines selected. The autofilter criteria are hard coded into the macro (e.g filter all items with description "Interim"). My problem is where there is no data with the the hard coded description in the particular batch of information I am running the macro on. Selection.AutoFilter Field:=45, Criteria1:="Interim" Range("AS3").Select ' This is the heading row in the column being filtered ActiveCell.Offset(1, 0).Select Range(Selection, Selection.End(xlDown)).Select Selection.SpecialCells(xlCellTypeVisible).Select Selection.EntireRow.Delete How do I get the macro to skip lines 2 to 6 above if there is no data with a description of Interim and to move onto the next hard coded description? Thanks in advance Craig |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofilter & delete lines
Thanks Bernie,
That worked brilliantly. I have a further step to take, which I tried to do by adapting you code. I want to filter column 52 for 0 values, and then apply a second filter on column 8 (H). I then want to apply a formula to column 52 (AZ) which refers to column 17 (Q). Itried this but it didn't work: Selection.AutoFilter Field:=52, Criteria1:="0" With Range(Range("H3"), Range("H3").End(xlDown)) .AutoFilter Field:=8, Criteria1:="Delivery and collection" .Offset(1, 44).SpecialCells(xlCellTypeVisible).Select .FormulaR1C1 = "=+RC[-38]/1.35" End With I assumed that the offset would take me to row 4, column AZ, but the formula gets put into H3, and refers to Column HR ( i.e 38 columns left of column H.) How do I get the macro to refer to Column Q and select the visible cells in Q, in which case the formula would be correct. Craig "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Craig, Actually, you don't need the On Error Resume Next: the offset ensures that at least one row is visible - the row immediately below your data table. HTH, Bernie MS Excel MVP "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Craig, Try On Error Resume Next With Range(Range("AS3"), Range("AS3").End(xlDown)) .AutoFilter Field:=1, Criteria1:="Interim" .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delet e .AutoFilter End With HTH, Bernie MS Excel MVP "Craig" wrote in message ... I have recorded a macro which autofilters data and then deletes the lines selected. The autofilter criteria are hard coded into the macro (e.g filter all items with description "Interim"). My problem is where there is no data with the the hard coded description in the particular batch of information I am running the macro on. Selection.AutoFilter Field:=45, Criteria1:="Interim" Range("AS3").Select ' This is the heading row in the column being filtered ActiveCell.Offset(1, 0).Select Range(Selection, Selection.End(xlDown)).Select Selection.SpecialCells(xlCellTypeVisible).Select Selection.EntireRow.Delete How do I get the macro to skip lines 2 to 6 above if there is no data with a description of Interim and to move onto the next hard coded description? Thanks in advance Craig |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofilter & delete lines
Craig,
Here's another brilliant effort ;-) HTH, Bernie With Range(Range("A3"), Range("AZ3").End(xlDown)) .AutoFilter Field:=52, Criteria1:="0" .AutoFilter Field:=8, Criteria1:="Delivery and collection" .Columns(52).Offset(1, 0).Resize(.Rows.Count - 1, 1) _ .SpecialCells(xlCellTypeVisible).FormulaR1C1 = "=+RC[-35]/1.35" .AutoFilter End With "Craig" wrote in message ... Thanks Bernie, That worked brilliantly. I have a further step to take, which I tried to do by adapting you code. I want to filter column 52 for 0 values, and then apply a second filter on column 8 (H). I then want to apply a formula to column 52 (AZ) which refers to column 17 (Q). Itried this but it didn't work: Selection.AutoFilter Field:=52, Criteria1:="0" With Range(Range("H3"), Range("H3").End(xlDown)) .AutoFilter Field:=8, Criteria1:="Delivery and collection" .Offset(1, 44).SpecialCells(xlCellTypeVisible).Select .FormulaR1C1 = "=+RC[-38]/1.35" End With I assumed that the offset would take me to row 4, column AZ, but the formula gets put into H3, and refers to Column HR ( i.e 38 columns left of column H.) How do I get the macro to refer to Column Q and select the visible cells in Q, in which case the formula would be correct. Craig "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Craig, Actually, you don't need the On Error Resume Next: the offset ensures that at least one row is visible - the row immediately below your data table. HTH, Bernie MS Excel MVP "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Craig, Try On Error Resume Next With Range(Range("AS3"), Range("AS3").End(xlDown)) .AutoFilter Field:=1, Criteria1:="Interim" .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delet e .AutoFilter End With HTH, Bernie MS Excel MVP "Craig" wrote in message ... I have recorded a macro which autofilters data and then deletes the lines selected. The autofilter criteria are hard coded into the macro (e.g filter all items with description "Interim"). My problem is where there is no data with the the hard coded description in the particular batch of information I am running the macro on. Selection.AutoFilter Field:=45, Criteria1:="Interim" Range("AS3").Select ' This is the heading row in the column being filtered ActiveCell.Offset(1, 0).Select Range(Selection, Selection.End(xlDown)).Select Selection.SpecialCells(xlCellTypeVisible).Select Selection.EntireRow.Delete How do I get the macro to skip lines 2 to 6 above if there is no data with a description of Interim and to move onto the next hard coded description? Thanks in advance Craig |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofilter & delete lines
Bernie,
I have discovered a problem with your earlier solution on deleting lines, but it is probably a result of me not applying it correctly. I have replicated the section, but changed the Interim Billing Instruction" with the next set of data I need to delete. For some reason the second set is not getting deleted, even though it is being selected by the filter. What have I done wrong? On Error Resume Next With Range(Range("e3"), Range("e3").End(xlDown)) .AutoFilter Field:=5, Criteria1:="Interim Billing Instruction" .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delet e End With On Error Resume Next With Range(Range("e3"), Range("e3").End(xlDown)) .AutoFilter Field:=5, Criteria1:="Team*" .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delet e End With Thanks Craig "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Craig, Here's another brilliant effort ;-) HTH, Bernie With Range(Range("A3"), Range("AZ3").End(xlDown)) .AutoFilter Field:=52, Criteria1:="0" .AutoFilter Field:=8, Criteria1:="Delivery and collection" .Columns(52).Offset(1, 0).Resize(.Rows.Count - 1, 1) _ .SpecialCells(xlCellTypeVisible).FormulaR1C1 = "=+RC[-35]/1.35" .AutoFilter End With "Craig" wrote in message ... Thanks Bernie, That worked brilliantly. I have a further step to take, which I tried to do by adapting you code. I want to filter column 52 for 0 values, and then apply a second filter on column 8 (H). I then want to apply a formula to column 52 (AZ) which refers to column 17 (Q). Itried this but it didn't work: Selection.AutoFilter Field:=52, Criteria1:="0" With Range(Range("H3"), Range("H3").End(xlDown)) .AutoFilter Field:=8, Criteria1:="Delivery and collection" .Offset(1, 44).SpecialCells(xlCellTypeVisible).Select .FormulaR1C1 = "=+RC[-38]/1.35" End With I assumed that the offset would take me to row 4, column AZ, but the formula gets put into H3, and refers to Column HR ( i.e 38 columns left of column H.) How do I get the macro to refer to Column Q and select the visible cells in Q, in which case the formula would be correct. Craig "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Craig, Actually, you don't need the On Error Resume Next: the offset ensures that at least one row is visible - the row immediately below your data table. HTH, Bernie MS Excel MVP "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Craig, Try On Error Resume Next With Range(Range("AS3"), Range("AS3").End(xlDown)) .AutoFilter Field:=1, Criteria1:="Interim" .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delet e .AutoFilter End With HTH, Bernie MS Excel MVP "Craig" wrote in message ... I have recorded a macro which autofilters data and then deletes the lines selected. The autofilter criteria are hard coded into the macro (e.g filter all items with description "Interim"). My problem is where there is no data with the the hard coded description in the particular batch of information I am running the macro on. Selection.AutoFilter Field:=45, Criteria1:="Interim" Range("AS3").Select ' This is the heading row in the column being filtered ActiveCell.Offset(1, 0).Select Range(Selection, Selection.End(xlDown)).Select Selection.SpecialCells(xlCellTypeVisible).Select Selection.EntireRow.Delete How do I get the macro to skip lines 2 to 6 above if there is no data with a description of Interim and to move onto the next hard coded description? Thanks in advance Craig |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofilter & delete lines
Craig,
You need to turn off the autofilter, then turn it back on. Also, in case you delete all the current lines, it is better to select from the bottom up, rather than the top down, so I've changed that. Also, I'm assuming that the values you want to filter on are in column E. Otherwise, you would need to expand the range to more columns (at least 5). This is tested and works. On Error Resume Next With Range(Range("E3"), Range("E65536").End(xlUp)) .AutoFilter Field:=1, Criteria1:="Interim Billing Instruction" .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delet e .AutoFilter End With With Range(Range("E3"), Range("E65536").End(xlUp)) .AutoFilter Field:=1, Criteria1:="Team*" .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delet e .AutoFilter End With HTH, Bernie MS Excel MVP "Craig" wrote in message ... Bernie, I have discovered a problem with your earlier solution on deleting lines, but it is probably a result of me not applying it correctly. I have replicated the section, but changed the Interim Billing Instruction" with the next set of data I need to delete. For some reason the second set is not getting deleted, even though it is being selected by the filter. What have I done wrong? On Error Resume Next With Range(Range("e3"), Range("e3").End(xlDown)) .AutoFilter Field:=5, Criteria1:="Interim Billing Instruction" .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delet e End With On Error Resume Next With Range(Range("e3"), Range("e3").End(xlDown)) .AutoFilter Field:=5, Criteria1:="Team*" .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delet e End With Thanks Craig "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Craig, Here's another brilliant effort ;-) HTH, Bernie With Range(Range("A3"), Range("AZ3").End(xlDown)) .AutoFilter Field:=52, Criteria1:="0" .AutoFilter Field:=8, Criteria1:="Delivery and collection" .Columns(52).Offset(1, 0).Resize(.Rows.Count - 1, 1) _ .SpecialCells(xlCellTypeVisible).FormulaR1C1 = "=+RC[-35]/1.35" .AutoFilter End With "Craig" wrote in message ... Thanks Bernie, That worked brilliantly. I have a further step to take, which I tried to do by adapting you code. I want to filter column 52 for 0 values, and then apply a second filter on column 8 (H). I then want to apply a formula to column 52 (AZ) which refers to column 17 (Q). Itried this but it didn't work: Selection.AutoFilter Field:=52, Criteria1:="0" With Range(Range("H3"), Range("H3").End(xlDown)) .AutoFilter Field:=8, Criteria1:="Delivery and collection" .Offset(1, 44).SpecialCells(xlCellTypeVisible).Select .FormulaR1C1 = "=+RC[-38]/1.35" End With I assumed that the offset would take me to row 4, column AZ, but the formula gets put into H3, and refers to Column HR ( i.e 38 columns left of column H.) How do I get the macro to refer to Column Q and select the visible cells in Q, in which case the formula would be correct. Craig "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Craig, Actually, you don't need the On Error Resume Next: the offset ensures that at least one row is visible - the row immediately below your data table. HTH, Bernie MS Excel MVP "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Craig, Try On Error Resume Next With Range(Range("AS3"), Range("AS3").End(xlDown)) .AutoFilter Field:=1, Criteria1:="Interim" .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delet e .AutoFilter End With HTH, Bernie MS Excel MVP "Craig" wrote in message ... I have recorded a macro which autofilters data and then deletes the lines selected. The autofilter criteria are hard coded into the macro (e.g filter all items with description "Interim"). My problem is where there is no data with the the hard coded description in the particular batch of information I am running the macro on. Selection.AutoFilter Field:=45, Criteria1:="Interim" Range("AS3").Select ' This is the heading row in the column being filtered ActiveCell.Offset(1, 0).Select Range(Selection, Selection.End(xlDown)).Select Selection.SpecialCells(xlCellTypeVisible).Select Selection.EntireRow.Delete How do I get the macro to skip lines 2 to 6 above if there is no data with a description of Interim and to move onto the next hard coded description? Thanks in advance Craig |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete options for AutoFilter? | Excel Worksheet Functions | |||
how to display the count of lines found by autofilter? | Excel Discussion (Misc queries) | |||
delete rows autofilter | Excel Programming | |||
Delete results of Autofilter??? | Excel Programming | |||
Removing Lines Identified by Autofilter | Excel Programming |