Deleting rows based on the dates condition

[johnabrosky]

Hello,

I have two tabs in by spreadsheet. "Report" and "Holidays". In the first tab I have data and one column contains dates (format: mm/dd/yyyy).
Also, in column A of Holidays tab I have listed several dates.

I would like to delete all rows from tab Report which are holidays listed in the second tab or weekend (Saturday, Sunday).

I found one code that works for holidays, but I don't understand how it works. Could someone be as kind to explain it in simplest words possible? Thank you in advance!

Sub Holidays()
Dim d As Object, e, rws&, cls&, i&, j&
Set d = CreateObject("scripting.dictionary")
For Each e In Sheets("Holidays").Range("A1").CurrentRegion
    d(e.Value) = 1
Next e
Sheets("ANA").Activate
rws = Cells.Find("*", after:=[a1], searchorder:=xlByRows, _
        searchdirection:=xlPrevious).Row
cls = Cells.Find("*", after:=[a1], searchorder:=xlByColumns, _
        searchdirection:=xlPrevious).Column
For i = rws To 1 Step -1
    For j = 1 To cls
        If d(Range("A1").Resize(rws, cls)(i, j).Value) = 1 Then _
            Cells.Rows(i).Delete: Exit For
Next j, i
End Sub

 

[sunnyland]

I didn't try to work out the code provided.
An easier to understand code is just basically:

1.Outside loop through rows in sheet "report"
2.Inside loop through rows in sheet "holidays"
3. Compare value: if match => delete row

In my sample, I have 2 worksheets:
1 Called "report"
1 Called "holidays"


Also I assume that data to compare are both in column 1 if not you will have to change the 1 to whatever column number you are using

This code may not be the most efficient in time but it seems to work and is quite simple to understand.
Try it on a copy of your workbook, so you can see if it does the intended job.

Let me know if it works for you.


With comments

Sub clearholidays()

Dim x
Dim z
'loop through all rows in the report sheet in column 1 - to change in not columns(column number)
For x = 1 To Sheets("report").UsedRange.Columns(1).Cells.Count

'loop through all rows in the holidays sheet in column 1 - to change in not columns(column number)
For z = 1 To Sheets("holidays").UsedRange.Columns(1).Cells.Count

'if report cell value = holidays cell value
If Sheets("report").Cells(x, 1) = Sheets("holidays").Cells(z, 1) Then

'delete entire row
Sheets("report").Cells(x, 1).EntireRow.Delete
End If
Next
Next

End Sub

Without comments

Sub clearholidays()
Dim x
Dim z
For x = 1 To Sheets("report").UsedRange.Columns(1).Cells.Count
For z = 1 To Sheets("holidays").UsedRange.Columns(1).Cells.Count
If Sheets("report").Cells(x, 1) = Sheets("holidays").Cells(z, 1) Then
Sheets("report").Cells(x, 1).EntireRow.Delete
End If
Next
Next
End Sub