Find only weekends in a range of dates

[Bravurian17]

I want to use VBA to find only Saturdays and Sundays from a range of dates and then put those in a new range on my worksheet. I'm ok with VBA, so whatever you throw at me will help. Thanks in advance.

 

[Colin Legg]

Hi,

Is this range of dates all in the same column? If yes, then using an autofilter might be a good option.

Otherwise, you could use a loop and check each cell in the range - something like the example here:
http://www.ozgrid.com/forum/showthread.php?t=80784


HTH,

 

[Georgiboy]

This will cut weekend dates from column A and place them in column C. Column A & C have headers...

Sub WkEndMover()
Dim rCell As Range
Dim endRowA As Long
Dim endRowC As Long

endRowA = Range("A" & Rows.Count).End(xlUp).Row

For Each rCell In Range("A2:A" & endRowA).Cells
    If Weekday(rCell.Value) = 7 Or Weekday(rCell.Value) = 1 Then
        endRowC = Range("C" & Rows.Count).End(xlUp).Row
        rCell.Cut Range("C" & endRowC + 1)
    End If
Next rCell

End Sub

Hope this helps

 

[Bravurian17]

Thanks Georgiboy
I used your code and it worked great, now i need help with this...

RED not part of coding

Sub wk_3()
Dim rCell As Range
Dim endRowD As Long
Dim dateFind
Dim dateFind2
endRowD = Admin.Range("D" & Rows.Count).End(xlUp).Row
For Each rCell In Admin.Range("D2:D" & endRowD).Cells
    dateFind = Day(rCell.Value)
        With ActiveSheet.Range("F1:AL1")
            dateFind2 = .Find(dateFind)
        End With
     [COLOR=red]~[/COLOR][COLOR=#ff0000]Format the cells entire column that i find.~[/COLOR]
Next rCell
End Sub

I want to format the cells column with:

    With [COLOR=red]"FoundCellsColumn"[/COLOR].Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 15790080
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    With [COLOR=red]"FoundCellsColumn"[/COLOR].Font
        .Size = 1
        .Color = -987136
        .TintAndShade = 0
    End With
    With [COLOR=red]"FoundCellsColumn"[/COLOR]
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .Value = "."
    End With

BUT: Nothing happens, i used a msgbox to verify that it was finding dateFind2 in the range. it displays the correct day and everything but when i try and get it to edit the column it won't work. please help. thanks in advance

oh and there is an easier way to shorten the coding for the second one that would be great

 

[Bravurian17]

ignore & Delete last post, not as detailed

RED not part of coding

Column D on the Admin worksheet contains weekends extracted from a list of Dates in Column A. This coding is on a userform. the activeworksheet is a schedule and columns G1 through AK1 are just numbers representing the days of the month (1-31) and are not formatted like dates

Sub wk_3()
Dim rCell As Range
Dim endRowD As Long
Dim dateFind
Dim dateFind2
endRowD = Admin.Range("D" & Rows.Count).End(xlUp).Row
For Each rCell In Admin.Range("D2:D" & endRowD).Cells
    dateFind = Day(rCell.Value)
        With ActiveSheet.Range("G1:AK1")
            dateFind2 = .Find(dateFind)
        End With
     [COLOR=red]~Format the cells that i find entire column.~[/COLOR]
Next rCell
End Sub

I want to format the cell found and the entire column, within the used range, with:

With [COLOR=red]"FoundCellsColumn"[/COLOR].Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 15790080
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    With [COLOR=red]"FoundCellsColumn"[/COLOR].Font
        .Size = 1
        .Color = -987136
        .TintAndShade = 0
    End With
    With [COLOR=red]"FoundCellsColumn"[/COLOR]
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .Value = "."
    End With

BUT: Nothing happens, i used a msgbox to verify that it was finding dateFind2 in the range. It finds and displays the correct day, but when i try to get it to edit the column itstead of a msgbox, it won't work. please help. thanks in advance

oh and is there an easier way to shorten the coding in the second example?

 

[Bravurian17]

This is a very stripped down version of my workbook, but it maintains the functionality of the code I would like to figure out. the idea is that the weekends will be highlighted with the blue. Thanks in advance

http://www.excelforum.com/attachmen...43312d1241945663-formatting-column-dummy.xlsm

 

[Georgiboy]

Maybe something like this...

Sub wk_3()
Dim rCell As Range
Dim endRowD As Long
endRowD = Sheet1.Range("D" & Rows.Count).End(xlUp).Row
On Error Resume Next
For Each rCell In Sheet1.Range("D2:D" & endRowD).Cells
        With ActiveSheet.Range("G1:AK1").Find(rCell.Value)
            .ColumnWidth = 10
            .Font.Bold = True
            .Interior.ColorIndex = 3
        End With
Next rCell
End Sub

Hope this gets you on your way