Macro to delete from autofilter (2003)

Topic

New Reply

Hello Everyone,

I recorded the macro below, but would like to know how to put a command so it would delete all rows that do not meet the criteria1:=”0.00″. I don’t know what the range would be of how many rows, however, the selection of the autofilter and criteria will not change just the will not change. Any help would be great.

Sub Sort ()

Selection.AutoFilter
ActiveWindow.SmallScroll ToRight:=2
Selection.AutoFilter Field:=15, Criteria1:=”0.00″
Rows(“5:95”).Select
Range(“C5”).Activate
ActiveWindow.ScrollRow = 88
ActiveWindow.ScrollRow = 86
ActiveWindow.ScrollRow = 84
ActiveWindow.ScrollRow = 82
ActiveWindow.ScrollRow = 78
ActiveWindow.ScrollRow = 74
ActiveWindow.ScrollRow = 70
ActiveWindow.ScrollRow = 66
ActiveWindow.ScrollRow = 62
ActiveWindow.ScrollRow = 58
ActiveWindow.ScrollRow = 53
ActiveWindow.ScrollRow = 49
ActiveWindow.ScrollRow = 45
ActiveWindow.ScrollRow = 43
ActiveWindow.ScrollRow = 41
ActiveWindow.ScrollRow = 39
ActiveWindow.ScrollRow = 37
ActiveWindow.ScrollRow = 35
ActiveWindow.ScrollRow = 33
ActiveWindow.ScrollRow = 31
ActiveWindow.ScrollRow = 29
ActiveWindow.ScrollRow = 27
ActiveWindow.ScrollRow = 23
ActiveWindow.ScrollRow = 21
ActiveWindow.ScrollRow = 19
ActiveWindow.ScrollRow = 17
ActiveWindow.ScrollRow = 15
ActiveWindow.ScrollRow = 13
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 5
ActiveWindow.ScrollRow = 1
Rows(“5:1201”).Select
Range(“C5”).Activate
Selection.Delete Shift:=xlUp
Range(“C145”).Select
Selection.AutoFilter
End Sub

Reply | Quote

Replies

Where are the column headings (field names) of the table?

Reply | Quote

Below is the macro to insert a row and assign names to columns.

Rows(“1:1”).Select
Selection.Insert Shift:=xlDown
Columns(“A:O”).Select
Columns(“A:O”).EntireColumn.AutoFit
ActiveWindow.SmallScroll Down:=-12
Range(“A1”).Select
ActiveCell.FormulaR1C1 = “Name”
Rows(“1:1”).Select
Selection.Font.Bold = True
Range(“B1”).Select
ActiveCell.FormulaR1C1 = “Month”
Range(“C1”).Select
ActiveCell.FormulaR1C1 = “Index”
Range(“D1”).Select
ActiveCell.FormulaR1C1 = “Car Type”
Range(“E1”).Select
ActiveCell.FormulaR1C1 = “model Type”
Range(“F1”).Select
ActiveCell.FormulaR1C1 = “Survey Activity”
Range(“G1”).Select
ActiveCell.FormulaR1C1 = “Day”
Range(“G1”).Select
Selection.AutoFill Destination:=Range(“G1:N1”), Type:=xlFillDefault
Range(“G1:N1”).Select
Range(“O1”).Select
ActiveCell.FormulaR1C1 = “Total”
Range(“O2”).Select
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
End Sub

Reply | Quote

The following macro will delete all rows NOT satisfying the criteria “0.00”.

Sub DeleteNonZero()
‘ Let Excel determine the range
With Range(“A1″).CurrentRegion
‘ Set filter to select values we want to delete
.AutoFilter Field:=15, Criteria1:=”0.00”
‘ Delete visible rows, but exclude the header row
.Offset(1, 0).Resize(RowSize:=.Rows.Count – 1) _
.SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
‘ Undo the filter
ActiveSheet.ShowAllData
End Sub

As you see, it is not necessary to scroll through the worksheet or to select cells.

Reply | Quote

Thank you hans.

Reply | Quote