Macro Button to hide rows based date in column

[nickpavlov]

Hello,

I am looking to create a macro button, that when clicked will auto hide rows that have a date older than 7 days in column K. (Sample attached)

I want to create a button, as I only want it to run when the button is clicked.

I appreciate any help with this.

Thank you,
Nick

[0Cool]

Check my attachment. Hope it is what is required by you.

[alansidman]

Attach this to a command button

Option Explicit

Sub HideRows()
Application.ScreenUpdating = False
Dim lr As Long, i As Long
lr = Range("A" & Rows.Count).End(xlUp).Row
For i = lr To 1 Step -1
If Range("K" & i) < Date - 7 Then
Range("A" & i).EntireRow.Hidden = True
End If
Next i
Application.ScreenUpdating = True

End Sub

You may want to look at this tutorial also

https://www.automateexcel.com/vba/un...-rows-columns/

[BadlySpelledBuoy]

Here's another version to do the same.

Sub HideOlderThan7Days()
    Dim rw As Range
    
    With ThisWorkbook.Sheets("Sheet1").ListObjects("Table1")
        For Each rw In .DataBodyRange.Rows
            If rw.Cells(1, "K").Value <= Date - 7 Then rw.Hidden = True
        Next
    End With
End Sub

BSB

[nickpavlov]

alansidman, I tested yours, but it hides everything, including the header and cells with no values. I forgot to have that option in my test file, there will be empty cells until a date is put in.

[nickpavlov]

BadlySpelledBuoy, I tested yours, and got a Run-Time error '9': Subscript out of range

[BadlySpelledBuoy]

It works for me. See attached.

I've updated the code to the below which accounts for cells without dates.

Sub HideOlderThan7Days()
    Dim rw As Range
    
    With ThisWorkbook.Sheets("Sheet1").ListObjects("Table1")
        For Each rw In .DataBodyRange.Rows
            If rw.Cells(1, "K") <= Date - 7 And rw.Cells(1, "K") <> "" Then rw.Hidden = True
        Next
    End With
End Sub

If you still get an error with this, then attach a more accurate sample file so we can investigate the reason properly.

BSB

[nickpavlov]

BadlySpelledBuoy, Thanks, it was my error, when I changed the worksheet name, I mistyped. It takes a few seconds to complete, but does exactly what I want. Thank you!

[alansidman]

Worked for me in your test file. You need to supply a file that is representative of your actual data if you expect results to work.

[nickpavlov]

Hi Alan, attached is an updated file, it was my error in the original post, sorry about that. In the updated file, it will hide rows that have no date in the table and also it hides the row 1.

[alansidman]

Try this:

Option Explicit

Sub HideRows()
    Application.ScreenUpdating = False
    Dim lr As Long, i As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row
    For i = lr To 4 Step -1

        If Not Range("K" & i) = "" And Range("K" & i) < Date - 7 Then
            Range("A" & i).EntireRow.Hidden = True
        End If
    Next i
    Application.ScreenUpdating = True

End Sub

[nickpavlov]

Awesome Alansidman, it runs quickly also.