Deleting Specific Rows - urgent

  • Thread starter Thread starter alexm999
  • Start date Start date
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?
 
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.
 
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)..
 
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
 
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
 
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
 
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
 
Weekdays starts in b7 cell and goes down to b37.
Days of the months start in a7 and go down to a37/
 
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...
 
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.
 
Back
Top