Macro maybe?

C

Connie Martin

In Col. A there are dates, formatted as dates in the format of Sun - Jan 01,
2010 and it goes to the end of 2010. How do I delete all the Saturday and
Sunday's in one shot? Connie
 
S

Sean Timmons

Format the column as Special/ ddd, filter, custom, equals Saturday or equals
Sunday, delete rows.
 
J

Jim Thomlinson

Here is a non macro solution. Change the format of your dates to a custom
format of dddd. Now all of your dates will be the days of the week. Sunday,
Monday, ...

Data | Filter | Auto Filter
Filter for Saturday.
Select the visible rows and delete.
Do the same for Sunday
Change the date formats back and remove the autofilter.
 
D

Don Guillett

Sub deletesatsun()
Dim md As String
For i = Cells(Rows.Count, 1) _
..End(xlUp).Row To 1 Step -1
md = Left(Format(Cells(i, 1), "ddd"), 3)
If md = "Sat" Or md = "Sun" Then
Rows(i).Delete 'MsgBox i
End If
Next i
End Sub
 
C

Connie Martin

Thank you! You and Jim had the same answer and it works wonderfully. I had
looked at that aspect but didn't know it would retain my original dates when
I reformatted it. I should've tried it. I actually hadn't thought of using
the filter. I was just trying to get them in alphabetical order so I could
delete all the Saturaday & Sunday rows manually, but the filter was much
quicker. Thanks again. Connie
 
C

Connie Martin

Thank you, Jim. You and Sean had the same answer. You'll see my response to
him. I am printing this post for future reference, as I always do. Thanks
again. Connie
 
C

Connie Martin

Don, I tried the macro as well as Jim and Sean's method, which work. I am
having a Compile Syntax error message with the macro, and the rows it's
highlighting are:
For i = Cells(Rows.Count, 1) _
...End(xlUp).Row To 1 Step -1
Anyway, since it can be done without a macro, I think I'll take that route.
Thank you for responding. Much appreciated. Connie
 
C

Connie Martin

Okay, this works now! Thank you. It's rather neat looking at the screen
"shaking" as it goes through the column removing the weekends! I'd shake,
too, if the weekends were removed from me! Ha! Thanks, Don. Another one to
print. Connie
 
D

Don Guillett

1st line
application.screenupdating=false
last line
application.screenupdating=true
 
Top