Deleting Specific Rows - urgent

A

alexm999

I have a column that lists the days of the months. Every month has
different days with the corresponding dates. Is there a way I can get
all of the SUNDAY rows to be deleted?
 
K

Kieran

In an adjacent column enter =weekday(your date) and copy down for the
length of the list.
Where 'your date' is a reference to the dates in your list.

Tehn autofilter the range for values of 1 (that is Sunday).

Then delete the rows, then delete the column of =weekday() formulas.

Hope it helps.
 
A

alexm999

Is there a way to automate the process using VB Script?
I want to create a Command Button that if pressed, will delete al
sundays data (the rows from a1 to whatever cell)..
 
K

Kieran

Sure it can be done, but I have no time now.

I will post back in a few hours (if someonelse does not get in first
 
K

Kieran

try this
Option Explicit

Sub DeleteSundays()
Application.ScreenUpdating = False
Dim I
I = [a1].End(xlDown).Row - 1
Do
If WeekDay([a1].Offset(I, 0).Value) = vbSunday Then
[a1].Offset(I, 0).EntireRow.Delete
End If
I = I - 1
If I < 0 Then Exit Do
Loop While [a1].Offset(I, 0).Value > 0 And I >= 0
Application.ScreenUpdating = True
End Su
 
A

alexm999

getting a VB error in command...
here's where the days start:
A7 all the way A37 are displayed as: 1/1/2004 and so on
B7 to B37 are the week days (monday, tuesday, etc...)

Can you help me revise your code
 
K

Kieran

Try Sub DeleteSundays()
Application.ScreenUpdating = False
Dim I
I = [a7].End(xlDown).Row - 1
Do
If WeekDay([a7].Offset(I, 0).Value) = vbSunday Then
[a1].Offset(I, 0).EntireRow.Delete
End If
I = I - 1
If I < 7 Then Exit Do
Loop While [a7].Offset(I, 0).Value > 0 And I >= 0
Application.ScreenUpdating = True
End Su
 
A

alexm999

Weekdays starts in b7 cell and goes down to b37.
Days of the months start in a7 and go down to a37/
 
A

alexm999

Cell A7 is 1/1/2004
Cell A8 is A7+1
Cell A8 is A8+1
and so on until it lists all 31 days of the month.
Cell B7 is =A7 (it's formated dddd to show the day only)
Cell B8 is =A8
and so on until it lists the correct days of the week.

I need a script that will help me delete all the sunday rows starting
in column C and beyond...
 
K

Kieran

Try this solution,

In row A8 enter =IF(WEEKDAY(A7)=7, A7+2, A7+1).

Then copy the formula down for all the rows in your list, the 'a7'
reference should update to a8, a9, etc automatically.

Then just enter the start date in A7 and the rest of the days should
continue automatically.
 
Top