VBA help

[ChrisFoster]

Hi all,

I have some code and I want to delete any rows where the cell in column A is "Yes".

Please can someone help?

Thanks,

Chris

 

[ChrisFoster]

I think you missed my reply above.
I worked it into your code below:

Sub MyDeleteRows()
    Range("A1").AutoFilter
    ActiveSheet.Range("$A$1:$S$2332").AutoFilter Field:=1, Criteria1:="Yes"
    Application.DisplayAlerts = False
    ActiveSheet.UsedRange.Offset(1, 0).Resize(ActiveSheet.UsedRange.Rows.Count - 1).Rows.Delete
    Application.DisplayAlerts = True
    Range("A1").AutoFilter
End Sub

Thanks Joe, yes I missed your first reply, apologies for that.

Your code hasnt worked either. It has kept my title line (row 1) but has deleted all data. I assume this is because 'Yes' is not present in column A. It works fine if 'Yes' is present though, which is really annoying.

 

[Joe4]

It has kept my title line (row 1) but has deleted all data. I assume this is because 'Yes' is not present in column A

Since you wanted to delete rows with "Yes" in them, I was assuming that you would have at least one row with a "Yes" in it.
However, it is a rather trivial amendment to make; simply check for the existence of at least one "Yes" first, i.e.

Sub MyDeleteRows()
    If Application.WorksheetFunction.CountIf(Range("A:A"), "Yes") > 0 Then
        Range("A1").AutoFilter
        ActiveSheet.Range("$A$1:$S$2332").AutoFilter Field:=1, Criteria1:="Yes"
        Application.DisplayAlerts = False
        ActiveSheet.UsedRange.Offset(1, 0).Resize(ActiveSheet.UsedRange.Rows.Count - 1).Rows.Delete
        Application.DisplayAlerts = True
        Range("A1").AutoFilter
    Else
        MsgBox "No rows to delete!"
    End If
End Sub

 

[ChrisFoster]

You're a genius Joe. Thanks so much.

 

[Joe4]

You are welcome.