How to Delte Multiple Rows Given a Value Existing

J

Jako

Ok so i now have this code


Option Explicit

Sub delete_rows()
Dim lastrow As Long
Dim row_index As Long
Application.ScreenUpdating = False
lastrow = ActiveSheet.Cells(Rows.Count, "F").End(xlUp).Row
For row_index = lastrow - 1 To 1 Step -1
If Cells(row_index, "F").Value = "Yellow" Then _
Rows(row_index).Delete
Next
lastrow = ActiveSheet.Cells(Rows.Count, "F").End(xlUp).Row
For row_index = lastrow - 1 To 1 Step -1
If Cells(row_index, "F").Value = "Green" Then _
Rows(row_index).Delete
Next
lastrow = ActiveSheet.Cells(Rows.Count, "F").End(xlUp).Row
For row_index = lastrow - 1 To 1 Step -1
If Cells(row_index, "F").Value = "Red" Then _
Rows(row_index).Delete
Next
lastrow = ActiveSheet.Cells(Rows.Count, "F").End(xlUp).Row
For row_index = lastrow - 1 To 1 Step -1
If Cells(row_index, "F").Value = "Blue" Then _
Rows(row_index).Delete
Next
Application.ScreenUpdating = True
End Sub


which is very bad programming i know.

Could anyone suggest how to tidy it up and also edit it so it runs th
cell value checks on every worksheet (could be upto 6) in th
workbook.

TI
 
D

Dave Peterson

Option Explicit
Sub delete_rows()
Dim lastrow As Long
Dim row_index As Long

Application.ScreenUpdating = False

lastrow = ActiveSheet.Cells(Rows.Count, "F").End(xlUp).Row
For row_index = lastrow - 1 To 1 Step -1
Select Case LCase(Cells(row_index, "F").Value)
Case Is = "yellow", "green", "red", "blue"
Rows(row_index).Delete
End Select
Next row_index

Application.ScreenUpdating = True
End Sub

Why did you use lastrow-1?

And I check for yellow, green, red, blue in any case: Red, RED, ReD, etc with
that lcase() stuff.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top