Delete Rows with particular dates

[Al Mackay]

Is it possible to delete rows where dates that appear in column C,
have been defined within Sheet2 (basically delete bank holiday days
that I'll separately define in Sheet2).

Also, after these dates have then been deleted to go through and
delete any rows where the date is either day 6 or 7 (e.g. Saturday and
Sundays).

Thanks In Advance, Cheers - Al.

 

[Ron de Bruin]

Hi Al

With the dates in sheet2 in column A
Select the cells in column C in Sheet1 or ? before you run the sub

Try this example

Public Sub FindNDelete()
Dim myRange As Range
Dim myCell As Range
Dim findText As String
Dim i As Long
Dim found As Boolean
Application.ScreenUpdating = False
found = False
For i = 1 To Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
findText = Worksheets("Sheet2").Cells(i, 1)
Application.StatusBar = "Finding " & findText
Set myRange = Selection
Do
Set myCell = myRange.Find(What:=findText, _
LookIn:=xlFormulas, lookAt:=xlWhole)
If Not myCell Is Nothing Then
myCell = ""
found = True
End If
Loop Until myCell Is Nothing
Next i
If found Then
myRange.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End If
Application.StatusBar = False
Application.ScreenUpdating = True
End Sub

 

[Mike Fogleman]

You can use Excel function Networkdays. You create a list of Holiday dates
for the function to use and it will also exclude days 6 & 7. Put this
function in an empty column adjoining your dates to test and use that one
date as the Start & End dates for the function ( a 1 day date range). The
result will be a 1 if the date is a work day or a 0 if it is day 6 or 7 or
on your Holiday list. Copy Fill-Down the function to the end of your data
and you will now have a 0 or 1 for each date in your data. Filter your
entire data by the 0's and delete them. All that is left are workdays.

Cheers...Mike F

 

[Guest]

Hi Ron,

Apols for the delay in responding to this posting. For some reason this
didn't delete any data?

I've read on other postings that people have problems with date formats?
Could this be a problem that I may have?

Would it be easier if I had on my second sheet all dates that I want it to
delete (incl' weekends in here and just have the VBA deleting on this
condition?).

Thanks for your help on this.

Cheers, Al.