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

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

"Connie Martin" wrote:

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

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.
--
HTH...

Jim Thomlinson


"Connie Martin" wrote:

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

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


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Connie Martin" wrote in message
...
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

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

"Sean Timmons" wrote:

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

"Connie Martin" wrote:

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

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

"Jim Thomlinson" wrote:

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.
--
HTH...

Jim Thomlinson


"Connie Martin" wrote:

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

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 a
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

"Don Guillett" wrote:

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


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Connie Martin" wrote in message
...
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

.

Was tested. somehow an EXTRA . in front of END. change to ONE dot.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Connie Martin" wrote in message
...
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 a
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

"Don Guillett" wrote:

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


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Connie Martin" wrote in message
...
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

.

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

"Don Guillett" wrote:

Was tested. somehow an EXTRA . in front of END. change to ONE dot.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Connie Martin" wrote in message
...
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 a
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

"Don Guillett" wrote:

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


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Connie Martin" wrote in message
...
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

.


.

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Connie Martin" wrote in message
...
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

"Don Guillett" wrote:

Was tested. somehow an EXTRA . in front of END. change to ONE dot.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Connie Martin" wrote in message
...
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 a
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

"Don Guillett" wrote:

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


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Connie Martin" wrote in
message
...
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

.


.