Check if column X contains date in the past, delete values from corresponding row in range.

[timzimm02816]

Hello everyone,
I am working on a macro where I would like to check if column range BI9:BI39 has a date value in the past (m/d/yyyy format) and if so delete the values in the same row - range BK9:BY39 otherwise do nothing.

Many thanks in advance

 

[mumps]

It is hard to work with a picture. It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[timzimm02816]

Thank you for the prompt response.

Here is a link to the file in question

dropbox.com

I'd like to check if the date in column range BI9:BI39 has a date in the past.

If date is in the past clear contents of corresponding row in range BK:BY

If date is equal or greater than today - do nothing

Example
BI9 = 1/1/20223
Clear contents BK9:BY9

 

[timzimm02816]

Missed in my other response... I'm working on the tab labeled "JAN"

 

[mumps]

Try:

Sub ClearRange()
    Application.ScreenUpdating = False
    Dim lRow As Long
    lRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row - 5
    Range("BI5:BO" & lRow).AutoFilter Field:=1, Criteria1:="<" & Date
    Range("BK9:BY" & lRow).SpecialCells(xlCellTypeVisible).ClearContents
    Range("BI5").AutoFilter
    Application.ScreenUpdating = True
End Sub

 

[timzimm02816]

Worked like a charm! Thank you so much!

 

[mumps]

You are very welcome.

 

[timzimm02816]

Spoke too soon

This works for the month of January, where we have some dates in the past.
I tried to run in Feb month tab and I am getting a 1004 error "no cells were found"

Would it make sense to try and add an IF block to this?

Sub ClearRange()
    Application.ScreenUpdating = False
    Dim lRow As Long
    lRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row - 5
  IF
  Range("BI5:BO" & lRow).AutoFilter Field:=1, Criteria1:="<" & Date
THEN
    Range("BK9:BY" & lRow).SpecialCells(xlCellTypeVisible).ClearContents
    Range("BI5").AutoFilter
ELSE
?do nothing?
End IF
    Application.ScreenUpdating = True
End Sub

 

[mumps]

Try:

Sub ClearRange()
    Application.ScreenUpdating = False
    Dim lRow As Long
    lRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row - 5
    Range("BI5:BO" & lRow).AutoFilter Field:=1, Criteria1:="<" & Date
    If [subtotal(103,BI:BI)] - 1 > 0 Then
        Range("BK9:BY" & lRow).SpecialCells(xlCellTypeVisible).ClearContents
    End If
    Range("BI5").AutoFilter
    Application.ScreenUpdating = True
End Sub

 

[timzimm02816]

Sorry for the delay getting back.
I was able to test the code in a live copy of my workbook and it worked perfectly!
Thank you again for your help!