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

 

[mole999]

record the action

apply filter on A for yes

select the rows and delete

then it can be tidied

 

[ChrisFoster]

record the action

apply filter on A for yes

select the rows and delete

then it can be tidied

That wont work as the data isn't always in the same order. So I'd end up deleting the incorrect rows.
Surely there is a one or two line code that allows me to do what I want?

 

[mole999]

thats why you filter on yes, the code wont care what line it is

 

[ChrisFoster]

It still wont work. For example, if I use the below code, it will always only ever delete row 7 as that is what the code is telling it to do.

    Range("A1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$S$2332").AutoFilter Field:=1, Criteria1:="Yes"
    Rows("7:7").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlUp

The other problem is what happens if 'Yes' is not present in column A? Then it would just debug at that stage anyway as it can't filter to the word 'Yes'.

 

[Joe4]

If you filter the data so that only the rows you want to see are displayed (the "Yes" rows), you can use this line of VBA code to delete them:

ActiveSheet.UsedRange.Offset(1, 0).Resize(ActiveSheet.UsedRange.Rows.Count - 1).Rows.Delete

 

[mole999]

    Range("A1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$S$20000").AutoFilter Field:=1, Criteria1:="Yes"

    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlUp

 

[ChrisFoster]

    Range("A1").Select[/FONT]
[FONT=Verdana]    Selection.AutoFilter[/FONT]
[FONT=Verdana]    ActiveSheet.Range("$A$1:$S$20000").AutoFilter Field:=1, Criteria1:="Yes"[/FONT]

[FONT=Verdana]    Range(Selection, Selection.End(xlDown)).Select[/FONT]
[FONT=Verdana]    Selection.Delete Shift:=xlUp

Thanks for this. It still hasnt worked though as it just deleted the cells from column A rather than deleting the whole row. Also, if the word 'Yes' is not present in row A then it just deletes the whole of column A.

 

[ChrisFoster]

I've tried to use the below code, which works fine if 'Yes' is present in column A, but it deletes all data other than row 1 if 'Yes' isnt present.

    With ActiveSheet
    .Range("A1:S1").AutoFilter 1, "Yes"
    .AutoFilter.Range.Offset(1).EntireRow.Delete
    End With

Any ideas how to skip this part if 'Yes' is not present?

 

[Joe4]

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