Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Cambridgeshire, USA
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro to delete from autofilter (2003)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro to delete from autofilter (2003)

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

  3. #3
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Cambridgeshire, USA
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to delete from autofilter (2003)

    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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro to delete from autofilter (2003)

    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.

  5. #5
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Cambridgeshire, USA
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to delete from autofilter (2003)

    Thank you hans.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •