Thread: Deleting Weekends and unrequired workhours

Ok , I have HUGE HUGE list of financial data covering up to 1 m+ rows in excel. In range a1 , i have the date , i want to delete rows which have weekends and contain workhours from 15:30 to 9:00am

Now i wrote a very very inefficient macro(coz i tried running it) , Is there a faster way , i have been reading about autofilter would be a faster way, but how can I achieve so ?

Code:

Sub weekend()
Dim j As Integer, 



For j = 1 To 34 ' i dont know how to get used number of rows 

If Weekday(Range("A1").Offset(j, 0), vbMonday) = 6 Then Range("A1").Offset(j, 0).EntireRow.Delete
If Weekday(Range("A1").Offset(j, 0), vbMonday) = 7 Then Range("A1").Offset(j, 0).EntireRow.Delete

Next j

Since my weekend dates were always in column 1 , i wrote/recorded this to achieve the same

Code:

   
    Columns("A:A").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A4").Select
    ActiveCell.FormulaR1C1 = "=TEXT(RC[1],""dddd"")"
    Range("A5").Select
    Selection.AutoFilter
    Range("A4").Select
    Selection.AutoFill Destination:=Range("A4:A42153")
    Range("A4:A42153").Select
    ActiveSheet.Range("$A$1:$C$42153").AutoFilter Field:=1, Criteria1:= _
        "=Saturday", Operator:=xlOr, Criteria2:="=Sunday"
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Delete Shift:=xlUp
    ActiveSheet.ShowAllData
    Columns("A:A").Delete

Very inefficient but gets the job done.