How to automatically delete rows in Excel based in day of week and date?

[pr0no]

Consider the following (partial) Excel worksheet:

    A  |   B         |  C    |  D
    ---+-------------+-------+-------
    id |  date       | var_a | var_b
    1  |  2011-03-12 | 200   | 34.22
    1  |  2011-03-13 | 203   | 35.13
    1  |  2011-03-14 | 205   | 34.14
    1  |  2011-03-15 | 207   | 54.88
    1  |  2011-03-16 | 208   | 12.01
    1  |  2011-03-17 | 208   | 12.01
    1  |  2011-03-18 | 203   | 76.10
    1  |  2011-03-19 | 210   | 14.86
    1  |  2011-03-20 | 200   | 25.45
    .  |  .          |  .    |  .
    .  |  .          |  .    |  .
    2  |  2011-03-12 | 200   | 34.22
    2  |  2011-03-13 | 203   | 35.13
    2  |  2011-03-14 | 205   | 34.14
    2  |  2011-03-15 | 207   | 54.88
    2  |  2011-03-16 | 208   | 12.01
    2  |  2011-03-17 | 208   | 12.01
    2  |  2011-03-18 | 203   | 76.10
    2  |  2011-03-19 | 210   | 14.86
    2  |  2011-03-20 | 200   | 25.45
    .  |  .          |  .    |  .
    .  |  .          |  .    |  .

In reality, there are over (the same) 500 dates for each of 100 companies (identified by id). I need to delete all rows which date falls on a saturday or sunday. In the example, March 12 and 13 (2011-03-12/13) and March 19 and 20 are Saturdays and Sundays. Second, I need to delete all rows with date 2011-07-04 (independence day; monday) and 2011-12-23 (friday before christmas).


Is this possible to do with either a formula or VBScript? I have never written a VBScript macro before (I have never had a use for it) so I would appreciate some help.

 

[Marcelo Branco]

Hi and welcome to Mr Excel Forum

Try Data > Advanced Filter

Take a look at
http://www.contextures.com/xladvfilter01.html (see the topic Extract Data to Another Worksheet)

and to use formulas in the Criteria Range see

http://www.contextures.com/xladvfilter02.html

You can use this formula
=AND(WEEKDAY(B2)<>7,WEEKDAY(B2)<>1,ISNA(MATCH(B2,Holidays,0)))

where Holidays is a named range containing 2011-07-04, 2011-12-23 and others holidays

M.

 

[JoeMo]

If you would like to try using VBA, this assumes your dates are in column B with either a header or date in B1:

Sub RemoveCertainDates()
Dim lR As Long, R As Range, dRw As Range, vA As Variant, i As Long
Const H1 As Date = #7/4/2011#
Const H2 As Date = #12/23/2011#

lR = Range("B" & Rows.Count).End(xlUp).Row
Set R = Range("B1", "B" & lR)
vA = R.Value
For i = LBound(vA, 1) To UBound(vA, 1)
    If IsDate(vA(i, 1)) Then
        Select Case Weekday(vA(i, 1))
            Case 1, 7, Weekday(H1), Weekday(H2)
                If dRw Is Nothing Then
                    Set dRw = R.Rows(i)
                Else
                    Set dRw = Union(dRw, R.Rows(i))
                End If
        End Select
    End If
Next i
If Not dRw Is Nothing Then dRw.EntireRow.Delete
End Sub

 

[cuchulainem]

This worked perfectly! Thank you!

If you would like to try using VBA, this assumes your dates are in column B with either a header or date in B1:

Sub RemoveCertainDates()
Dim lR As Long, R As Range, dRw As Range, vA As Variant, i As Long
Const H1 As Date = #7/4/2011#
Const H2 As Date = #12/23/2011#

lR = Range("B" & Rows.Count).End(xlUp).Row
Set R = Range("B1", "B" & lR)
vA = R.Value
For i = LBound(vA, 1) To UBound(vA, 1)
    If IsDate(vA(i, 1)) Then
        Select Case Weekday(vA(i, 1))
            Case 1, 7, Weekday(H1), Weekday(H2)
                If dRw Is Nothing Then
                    Set dRw = R.Rows(i)
                Else
                    Set dRw = Union(dRw, R.Rows(i))
                End If
        End Select
    End If
Next i
If Not dRw Is Nothing Then dRw.EntireRow.Delete
End Sub

 

[JoeMo]

This worked perfectly! Thank you!

You are welcome - thanks for the reply.