How to Delte Multiple Rows Given a Value Existing
 

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

--
Jak
-----------------------------------------------------------------------
Jako's Profile: http://www.excelforum.com/member.php...nfo&userid=871
View this thread: http://www.excelforum.com/showthread.php?threadid=27169

How to Delte Multiple Rows Given a Value Existing
 

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.



Jako wrote:

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 the
cell value checks on every worksheet (could be upto 6) in the
workbook.

TIA

--
Jako
------------------------------------------------------------------------
Jako's Profile: http://www.excelforum.com/member.php...fo&userid=8710
View this thread: http://www.excelforum.com/showthread...hreadid=271697

--

Dave Peterson