not one sheet but all !

[Bob Phillips]

J_J,

your code works fine for me, with one small exception.

Because you step from lastrow - 1 it doesn't delete the last row ifcolumn F
has a colour. You should step down from lastrow.

What happens for you?

HTH

RP

 

[Bob Phillips]

Think I've got it.

Is all that code in a sheet module, for Sheet 1?

If so, move the Sub delete_ygrb_rows() and the sub AllSheets() into a
standard code module and it should work.

My previous comment on lastrow still holds.

 

[Dave Peterson]

See one more reply at your other thread.

Hi,
I am trying to adopt a code to delete all rows in a workbooks' all sheets if
a number of criteria is valid.
This is as far as I've gone. But the code only works for Sheet1.
Can you correct my mistake?
------------------------------

Option Explicit
Sub delete_ygrb_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

Sub AllSheets()
Dim mySht As Worksheet
For Each mySht In ActiveWorkbook.Worksheets
mySht.Activate
Call delete_ygrb_rows
Next mySht
End Sub

Private Sub CommandButton1_Click()
AllSheets
End Sub

 

[Bob Phillips]

Thanks for telling us J_J

Bob

Yesss....... just tested it!
Now it does the job perferctly.
Thank you very much Bob.
Cheers
J_J

 

[J_J]

Hi,
I am trying to adopt a code to delete all rows in a workbooks' all sheets if
a number of criteria is valid.
This is as far as I've gone. But the code only works for Sheet1.
Can you correct my mistake?
------------------------------

Option Explicit
Sub delete_ygrb_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

Sub AllSheets()
Dim mySht As Worksheet
For Each mySht In ActiveWorkbook.Worksheets
mySht.Activate
Call delete_ygrb_rows
Next mySht
End Sub

Private Sub CommandButton1_Click()
AllSheets
End Sub

 

[Dave Peterson]

I didn't see a followup to my followup <bg>.

But this is the change I suggested:

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

Application.ScreenUpdating = False

For Each wks In ActiveWorkbook.Worksheets
With wks
lastrow = .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
End With
Next wks

Application.ScreenUpdating = True
End Sub

 

[J_J]

Bob, I think you did it again...
I'll try and get back to this thread to thank you.
Sincerely
J_J

 

[J_J]

Yesss....... just tested it!
Now it does the job perferctly.
Thank you very much Bob.
Cheers
J_J

 

[J_J]

Thank you Dave. I think I've replied to you there. If not sorry about it.
You are among my favorites in this NG.
Cheers
J_J

 

[Dave Peterson]

Sometimes not activating sheets is important. (Sometimes, it's not.)

Thank you Dave. I must have posted (replied) to a wrong group/thread. But
your code works perfectly well...
Sincerely
J_J

 

[J_J]

Thank you Dave. I must have posted (replied) to a wrong group/thread. But
your code works perfectly well...
Sincerely
J_J