Delete non weekdays

[Arne Hegefors]

Hi! I have long lists of dates that I often have to clean by deleting all
dates that are weekend dates. I am trying to write a sub that does this for
me (i know how to do it in a worksheet but it takes too long time since i
have very many books..). I tried selction the dates that I want to clean and
then running the macro but that obviously does not work. Is it posiible to do
this in any way? any help appreciated!

Sub deleteWeekEnds()
Dim i As Integer
i = Weekday(Selection, vbMonday)
If i = 6 Or i = 7 Then
Selection.Row.Delete
End If
End Sub

 

[Gary''s Student]

Sub arne()
Dim d As Date
d = Selection.Value
n = Application.WorksheetFunction.Weekday(d)
If n = 7 Or n = 1 Then
Selection.EntireRow.Delete
End If
End Sub


and remember that if this is in a loop, run the loop backwards

 

[paul.robinson]

Hi! I have long lists of dates that I often have to clean by deleting all
dates that are weekend dates. I am trying to write a sub that does this for
me (i know how to do it in a worksheet but it takes too long time since i
have very many books..). I tried selction the dates that I want to clean and
then running the macro but that obviously does not work. Is it posiible to do
this in any way? any help appreciated!

Sub deleteWeekEnds()
Dim i As Integer
i = Weekday(Selection, vbMonday)
If i = 6 Or i = 7 Then
Selection.Row.Delete
End If
End Sub

Hi
Try this:

Sub deleteWeekEnds()
Dim i As Integer, DateCell As Range
For Each DateCell In Selection
i = Weekday(DateCell, vbSaturday)
If (i = 0 Or i = 1) Then
DateCell.EntireRow.Delete
End If
Next DateCell
End Sub

regards
Paul

 

[Arne Hegefors]

Thanks alot for your answer! However the code works only if just one cell is
selected. If many cells are selected it does not work. Is it posiible to fix
this? Thanks!

"Gary''s Student" skrev:

 

[Gary''s Student]

We will build a Union and then do only one delete row:

Sub arne2()
Dim d As Date
Set rdel = Nothing
For Each r In Selection
d = r.Value
n = Application.WorksheetFunction.Weekday(d)
If n = 7 Or n = 1 Then
If rdel Is Nothing Then
Set rdel = r
Else
Set rdel = Union(rdel, r)
End If
End If
Next

If rdel Is Nothing Then
Else
rdel.EntireRow.Delete
End If
End Sub

 

[Ron Rosenfeld]

Hi! I have long lists of dates that I often have to clean by deleting all
dates that are weekend dates. I am trying to write a sub that does this for
me (i know how to do it in a worksheet but it takes too long time since i
have very many books..). I tried selction the dates that I want to clean and
then running the macro but that obviously does not work. Is it posiible to do
this in any way? any help appreciated!

Sub deleteWeekEnds()
Dim i As Integer
i = Weekday(Selection, vbMonday)
If i = 6 Or i = 7 Then
Selection.Row.Delete
End If
End Sub

What you need to do is select the bottom row in your range, and then step
upwards, testing and deleting as you go.

I'm not sure what the most efficient way in your worksheet is to select the
bottom row, so I just used column A, but you can change this in many ways.

Here's an example:

=======================================
Option Explicit
Sub DelWeekends()
Dim LastRow As Range, c As Range
Dim i As Long
'you should change this to reflect the appropriate column
Set LastRow = Range("a65535").End(xlUp)

For i = LastRow.Row To 1 Step -1
Set c = Cells(i, 1)
If IsDate(c) Then
If Weekday(c) = vbSaturday Or Weekday(c) = vbSunday Then
c.EntireRow.Delete
End If
End If
Next i
End Sub
====================================
--ron