Delete Sat & Sun

[billandrew]

Afternoon

Is there a more appropriate way to delete row if value is Saturday or Sunday than the following

Sub deleteSatSun()

Dim dlrow As Long, ws As Worksheet
Set ws = ActiveSheet
For dlrow = ws.Range("B" & Rows.Count).End(xlUp).Row To 2 Step -1
If ws.Range("B" & dlrow).Value = "Sunday" Then
Rows(dlrow).EntireRow.Delete
ElseIf ws.Range("B" & dlrow).Value = "Saturday" Then
Rows(dlrow).EntireRow.Delete
End If
Next dlrow
End Sub

 

[hiker95]

billandrew,

Here are two macro examples.

Please TEST them FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

The first one will loop thru the rows in column B, from the last used row to row 2.

Sub deleteSaturdaySunday_V1()
' hiker95, 08/07/2017, ME1017934
Dim dlrow As Long, r As Long
Application.ScreenUpdating = False
With ActiveSheet
  dlrow = .Range("B" & Rows.Count).End(xlUp).Row
  For r = dlrow To 2 Step -1
    If .Range("B" & r).Value = "Sunday" Or .Range("B" & r).Value = "Saturday" Then
      .Rows(r).EntireRow.Delete
    End If
  Next r
End With
Application.ScreenUpdating = True
End Sub

The next macro will not loop thru the rows in column B, and, should be very fast:

Sub deleteSaturdaySunday_V2()
' hiker95, 08/07/2017, ME1017934
Dim Addr As String
Addr = "B2:B" & Cells(Rows.Count, "B").End(xlUp).Row
Range(Addr) = Evaluate(Replace("IF(@=""Saturday"",""#N/A"",@)", "@", Addr))
Range(Addr) = Evaluate(Replace("IF(@=""Sunday"",""#N/A"",@)", "@", Addr))
On Error GoTo NoDeletes
Columns("B").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
NoDeletes:
End Sub

 

[billandrew]

Both work excellent with no issues. Unfortunately I do not quite understand the second code. Takes time I guess.

Thank You as always

 

[hiker95]

billandrew,

The second one is one of the gems from/by Rick Rothstein, MrExcel MVP.

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.