deleting rows based on value of cells in column

[Keyss]

So hi,

First off, thank you to NoSparks who helped me previously, was a great help & deserves a 'callout.'

Issue I need help with.

I'm trying to delete rows from a table based on a value contained of cells in a particular column. I can do it manually however using the recorded macro, it deletes 'all' information in the spreadsheet.

I'm highlighting a column that has the specific value 'Yes' in it that i want to delete the rows of. I CRTL F, do 'find all' on value 'Yes', then CRTL A to highlight all these cells. Next step is closing the FIND / FIND REPLACE box and CRTL -, choose Entire Row in the Delete box and click the OK button. This is when it deletes ALL data in the spreadsheet instead of just the rows of the highlighted cells.

Code below

Sub Macro4()
'
' Macro4 Macro
'


'
Windows("usage for Macro 4 code example.xls").Activate
Columns("G:G").Select
Selection.EntireRow.Delete
Windows("! Contract review Macro APP.xlsm").Activate
End Sub


As you can see it records nothing of the specific "Yes" value in which to delete the rows containing and I don't know enough yet to work it out.

Thank you for any assistance.

 

[fhqwgads]

try use this once you have all your yes cells selected

Sub deleterowstest1()

Selection.EntireRow.Delete

End Sub

 

[My Aswer Is This]

So are you saying if Yes is in column G you want that entire row deleted

So if Row(3) column G has Yes then you want Row(3) deleted is this what you want.

And if Yes in in Row(5) column G you want Row(5) deleted.

 

[Michael M]

Maybe something like this...
It will delete all rows that have a "Yes" in Column "G"....you don't need to select them, just run the code on the active sheet

Sub MM()
With Columns("G")
    .AutoFilter Field:=1, Criteria1:="Yes"
    .Resize(Rows.Count - 1).Offset(1).EntireRow.Delete
    .AutoFilter
End With
End Sub

 

[vcoolio]

Perhaps.....

Sub DeleteRws()

Application.ScreenUpdating = False

With ActiveSheet.[A1].CurrentRegion
                .AutoFilter 7, "Yes"
                .Offset(1).EntireRow.Delete
                .AutoFilter
End With
                
Application.ScreenUpdating = True

End Sub

I hope that this helps.

Cheerio,
vcoolio.

 

[Keyss]

fhqwgads - this deleted the entire data on the sheet

My Aswer Is This - answer, yes that is exactly what i am trying to achieve

Michael M - came up with running error

Run-Time error '1004': The command could not be completed by using the range specified. Select a single cell within the range and try the command again.
In debug the following is highlighted - .Autofilter Field:=1, Criterial:="Yes"

 

[vcoolio]

Hi Keyss,

Does the code in post #5 work?

Cheerio,
vcoolio.

 

[Michael M]

do you have a header row on the sheet ??

 

[Keyss]

vcoolio,

Thank you so much, it worked. I'm very excited!!

And thank you to everyone that offered assistance.

I can't wait until I know enough to be able to help somebody else.

Thanks all

 

[vcoolio]

You're welcome Keyss.

Glad we could help.

Cheerio,
vcoolio.