Autofilter & delete lines

C

Craig

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
 
B

Bernie Deitrick

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.Delete
.AutoFilter
End With

HTH,
Bernie
MS Excel MVP
 
T

Tom Ogilvy

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
 
B

Bernie Deitrick

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
 
C

Craig

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
 
B

Bernie Deitrick

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 said:
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 said:
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
Click to expand...
Click to expand...
 
C

Craig

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.Delete
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.Delete
End With


Thanks

Craig



Bernie Deitrick said:
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 said:
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 said:
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


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.Delete
.AutoFilter
End With

HTH,
Bernie
MS Excel MVP


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
Click to expand...
Click to expand...
Click to expand...
 
B

Bernie Deitrick

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.Delete
.AutoFilter
End With

With Range(Range("E3"), Range("E65536").End(xlUp))
.AutoFilter Field:=1, Criteria1:="Team*"
.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
.AutoFilter
End With

HTH,
Bernie
MS Excel MVP


Craig said:
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.Delete
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.Delete
End With


Thanks

Craig



Bernie Deitrick said:
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 said:
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

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


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.Delete
.AutoFilter
End With

HTH,
Bernie
MS Excel MVP


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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Unable to delete rows for only filtered results 4
Excel Macro to use cells as a autofilter 0
Trying to insert rows in a Macro 5
Help me4 2
Exclude header row when copying autofilter results? (XL2003) 1
Catch Error in macro 4
Paste failing after cut 7
"Run-time error '1004' 5

Top