Deleting rows with criteria met

Re: Deleting rows with criteria met

Try this:

Sub Delete_rows()
Dim end_row as integer
end_row = Cells(65536,4).End(xlUp).Row


For i = 2 to end_row
If Cells(i,4).Value = "Yes" Then
Rows(i).Delete
End If
Next


End Sub

Alastair

Re: Deleting rows with criteria met

Sub Delete_rows() 
    Dim end_row As Integer 
    end_row = Sheets("Sheet1").Cells(65536,4).End(xlUp).Row 'Change Sheet1 here...
     
    For i = 2 To end_row 
        If Sheets("Sheet1").Cells(i,4).Value = "Yes" Then '...and here
            Sheets("Sheet1").Rows(i).Delete '...and here
        End If 
    Next 
     
End Sub

From the info you have given the cells will not need updating.

Hope this helps

Alastair

Re: Deleting rows with criteria met

How can I modify this code to delete certain rows based on the date which is in the cells. I need to delete all other rows except the ones contained in a 2 week interval following the date in the activecell.

So basically after i select a certain row which contains a date, I want teh program to find the date 2 weeks ahead(14 days) select and keep all that data an delete the rest of the rows. Catch is if the date 2 weeks ahead is a weekend date then the macro should go back 1 day at a time until the date of the friday is found in the Column A being checked. Thanks for the help.

Re: Deleting rows with criteria met

If you are deleting rows, it is best to work from the bottom up, i.e.

For i = end_row To 2 Step -1

This way you avoid the problem of missing 1 row where you have two consecutive rows to delete. For example, if rows 4 and 5 both contain the word Yes, when you delete row 4, row 5 becomes row 4, but on your next loop the counter has increased to 5 and is checking the new row 5 for =Yes.

Hope this helps.

Regards,
Batman.