If No Value

[billandrew]

I have the following code to delete a row if the value in column C is Monday. My question is, if there is no Monday value will there be an issue, so far I have run the code and I do not see any problems. Is there an alternative.

Thank You

Code

Sub DeleteMonday()




Dim lr As Long
Dim w As Long

Sheets("Sheet1").Activate

lr = Range("C" & Rows.Count).End(xlUp).Row


For w = 2 To lr

If Cells(w, "C").Value = "Monday" Then

Cells(w, "C").EntireRow.Delete

End If

Next w

End Sub

 

[My Aswer Is This]

When deleting rows you should run the loop backwards
If no "Monday" is found you should have no problems
See code marked in red for changes made to your script.

Sub DeleteMonday()
Application.ScreenUpdating = False
Dim lr As Long
Dim w As Long
Sheets("Sheet1").Activate
lr = Range("C" & Rows.Count).End(xlUp).Row

    [COLOR=#ff0000]For w = lr To 2 Step -1[/COLOR]
        If Cells(w, "C").Value = "Monday" Then
            Cells(w, "C").EntireRow.Delete
        End If
    Next w
Application.ScreenUpdating = True
End Sub

 

[Fluff]

Is there an alternative.

Yes, here's one

Sub I_Dont_Like_Monday()
   With Sheets("Sheet1").Columns(3)
      .Replace "Monday", "#N/A", xlWhole, , False, , False
      On Error Resume Next
      .SpecialCells(xlConstants, xlErrors).EntireRow.Delete
      On Error GoTo 0
   End With
End Sub

 

[billandrew]

Thank you gentlemen..

 

[My Aswer Is This]

Here is a way to do it with filter which may be faster.

Sub Filter_Monday()
Application.ScreenUpdating = False
Dim ans As String
On Error GoTo M
ans = "Monday"
    
    With ActiveSheet.Range(Cells(1, 3), Cells(Cells(Rows.Count, 3).End(xlUp).Row, 3))
        .AutoFilter Field:=1, Criteria1:=ans
        .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        .AutoFilter
    End With
    
Exit Sub
M:
MsgBox "No Rows with your criteria found"
ActiveSheet.AutoFilterMode = False
Application.ScreenUpdating = True
End Sub

 

[billandrew]

Works well

Thanks again

 

[Fluff]

Glad we could help & thanks for the feedback