Macro maybe?

  • Thread starter Thread starter Connie Martin
  • Start date Start date
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
 
Format the column as Special/ ddd, filter, custom, equals Saturday or equals
Sunday, delete rows.
 
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.
 
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
 
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
 
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
 
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
 
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
 
1st line
application.screenupdating=false
last line
application.screenupdating=true
 
Back
Top