vba to delete rows base on criteria

[cjcass]

Hi,
I have the code below which works but has an issue in that it always deletes Row 9 whether it meets the criteria or not - any help much appreciated.
Thanks

Dim lr&
Application.ScreenUpdating = False
ActiveSheet.AutoFilterMode = False
lr = Cells(Rows.Count, "CZ").End(xlUp).Row
    With Range("CZ9:CZ" & lr)
        .AutoFilter field:=1, Criteria1:="<" & Range("CM3").Value
        .SpecialCells(xlCellTypeVisible).EntireRow.delete
    End With
ActiveSheet.AutoFilterMode = False
Application.ScreenUpdating = True

 

[rlv01]

Not tested.

    Dim lr&
    Dim FilterRange As Range, DataRange As Range, DeleteRange As Range
    Application.ScreenUpdating = False
    ActiveSheet.AutoFilterMode = False
    lr = Cells(Rows.Count, "CZ").End(xlUp).Row
    Set FilterRange = Range("CZ8:CZ" & lr)
    Set DataRange = FilterRange.Offset(1).Resize(FilterRange.Rows.Count - 1)
    FilterRange.AutoFilter field:=1, Criteria1:="<" & Range("CM3").Value
    Set DeleteRange = Application.Intersect(FilterRange.SpecialCells(xlCellTypeVisible).EntireRow, DataRange.EntireRow)
    If Not DeleteRange Is Nothing Then
        DeleteRange.Delete                            'delete blank rows
    End If
    ActiveSheet.AutoFilterMode = False
    Application.ScreenUpdating = True

 

[MARK858]

@cjcass AutoFilter always requires a header row (i.e. one row above the data), which is why your first row (row 9) remains visible whatever the criteria and is picked up by the SpecialCells.

If your data starts in row 9 then you need to make your range start on row 8 and then offset it as @rlv01 has done in the code in post number 2.

You don't actually need the SpecialCells as only the visible cells are deleted when you use .Delete on filtered cells if none of the rows are manually hidden.

 

[cjcass]

Not tested.

    Dim lr&
    Dim FilterRange As Range, DataRange As Range, DeleteRange As Range
    Application.ScreenUpdating = False
    ActiveSheet.AutoFilterMode = False
    lr = Cells(Rows.Count, "CZ").End(xlUp).Row
    Set FilterRange = Range("CZ8:CZ" & lr)
    Set DataRange = FilterRange.Offset(1).Resize(FilterRange.Rows.Count - 1)
    FilterRange.AutoFilter field:=1, Criteria1:="<" & Range("CM3").Value
    Set DeleteRange = Application.Intersect(FilterRange.SpecialCells(xlCellTypeVisible).EntireRow, DataRange.EntireRow)
    If Not DeleteRange Is Nothing Then
        DeleteRange.Delete                            'delete blank rows
    End If
    ActiveSheet.AutoFilterMode = False
    Application.ScreenUpdating = True

Thank you very much indeed that works perfectly!

 

[cjcass]

@cjcass AutoFilter always requires a header row (i.e. one row above the data), which is why your first row (row 9) remains visible whatever the criteria and is picked up by the SpecialCells.

If your data starts in row 9 then you need to make your range start on row 8 and then offset it as @rlv01 has done in the code in post number 2.

You don't actually need the SpecialCells as only the visible cells are deleted when you use .Delete on filtered cells if none of the rows are manually hidden.

Thank you for the explanation - it now makes sense to me why it wasn't working - I had a hunch it was something to do with the header but I couldn't work it out.

 

[rlv01]

Thank you very much indeed that works perfectly!

You're welcome.