1

I need to delete all the rows with the value 5.0.0 and I create/use an autofilter to filter that value.

The fourth line is where I delete all the rows containing the value. When I do this, the columns headers also become deleted.
Using ws. might be easier, however the names of the sheets will change over time, so I want to avoid using that.

Columns ("L").Select
Selection.Autofilter
ActiveSheet.Range("$F$1:$H$13889").AutoFilter Field:=1, Criteria1:="5.0.0"
ActiveCell.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
Community
  • 1
  • 1
User72
  • 29
  • 4
  • See [Delete row based on partial text](https://stackoverflow.com/questions/11317172/delete-row-based-on-partial-text/11317372#11317372) – Siddharth Rout Aug 10 '20 at 05:06

1 Answers1

3

I think your Range("$F$1...) is messing you up. Try this:

Option Explicit

Sub DeleteFilteredRows()

   Dim rngFltr As Range
   Dim lLastRow As Long
   
   Application.ScreenUpdating = False  'Keep screen from flickering
   lLastRow = Cells(Rows.Count, "L").End(xlUp).Row
   
   Set rngFltr = Range(Cells(1, "L"), Cells(lLastRow, "L"))
   
   With rngFltr
     .AutoFilter Field:=1, Criteria1:="5.0.0"
     .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
   End With 'rngFltr
    
   ActiveSheet.AutoFilterMode = False  'Clear Filter
    
   Application.ScreenUpdating = True   'Update Screen
   
End Sub 'DeleteFilteredRows

HTH

RetiredGeek
  • 2,980
  • 1
  • 7
  • 21
  • Can I ask what exactly does Application.ScreenUpdating = True do? – User72 Aug 10 '20 at 04:44
  • It is common practice to turn off screen updating Application.ScreenUpdating = False to speed up large operations by not refreshing the screen until all calculations are completed. Application.ScreenUpdating = True turns the screen back on. – RetiredGeek Aug 10 '20 at 17:43