Clear contents of specific rows given criteria

[Edward Sky]

Hey all,

Love this place of wisdom! May I please have a macro button on the same sheet that says:

If start date in cells D10:D100 has passed today's date and end date in cells E10:E100 has also passed today's date, delete values in B10:E100 and G10:I100, then sort column D10:D100 by oldest to newest...

 

[Oeldere]

Why do you not record the manualy actions.

Then you have the code.

 

[Edward Sky]

How do I begin a macro to look at cells and check if it's less than today's date? I wouldn't know where to start I'm afraid. If you teach me to record a macro to achieve this, that will be all the assistance I need.

 

[Oeldere]

view => macro => record macro.

 

[Fluff]

Hia
This should do the 1st part

Sub Fluff6()

    Dim usdrws As Long
    Dim i As Long
    
    With Sheets("Holidays")
        usdrws = .Range("D" & Rows.Count).End(xlUp).Row
        For i = 2 To usdrws
            If .Range("D" & i) < Date And .Range("E" & i) < Date Then
                .Range("D" & i) = "True"
            End If
        Next i
    End With

    With Range("D2:D" & usdrws).SpecialCells(xlCellTypeConstants, xlLogical)
        .Offset(, 3).Resize(, 3).Clear
        .Offset(, -2).Resize(, 4).Clear
    End With

' Sort macro goes here

End Sub

If you record a macro to do the sort, you can then add that to the end of this

 

[Edward Sky]

This was perfect until I realised it cleared all my formatting, im sorry I didn't specify that I need only the values deleted. Also, can you add in a dummy macro at the end that does a sort please? I tried copying and pasting my macro but got an error.

 

[Fluff]

Try

Sub Fluff6()

    Dim usdrws As Long
    Dim i As Long
    
    With Sheets("Holidays")
        usdrws = .Range("D" & Rows.Count).End(xlUp).Row
        For i = 2 To usdrws
            If .Range("D" & i) < Date And .Range("E" & i) < Date Then
                .Range("D" & i) = "True"
            End If
        Next i
    End With

    With Sheets("Holidays").Range("D2:D" & usdrws).SpecialCells(xlCellTypeConstants, xlLogical)
        .Offset(, 3).Resize(, 3).ClearContents
        .Offset(, -2).Resize(, 4).ClearContents
    End With
    With Sheets("Holidays")
        .Cells.Sort Key1:=.Range("D1"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    End With

End Sub

One thing to bear in mind, is that I'm running Xl 2003 & there were changes made to the sort when 2007 came out.
So while this may work, it may not be the best way to do it.