Scan five columns and delete rows that don't have today's date

[kelly mort]

Hi,
I need a script that will scan from E4 to last used row then G4, I4, K4 and M4 for today's date. So any row which does not have today's date in the said columns, delete them.

I am stacked so I need someone pull me out.

Thanks

 

[Peter_SSs]

But this deletes the date in the InputBox.

It doesn't for me. It deletes all rows that do not contain the entered date and retains rows that do contain the entered date.

 

[kelly mort]

Oh sure!!!


I missed something . Working very great

Thanks

 

[Peter_SSs]

Working very great

Thanks

Glad you resolved it.

 

[kelly mort]

Try

Sub Del_Rows_v2()
  Dim Resp As String
  
  Resp = InputBox("Enter date of interest")
  If IsDate(Resp) Then
    Application.ScreenUpdating = False
    Range("N2").Formula = "=COUNTIF(G4:M4," & CLng(DateValue(Resp)) & ")=0"
    With Range("A3:M" & Range("A" & Rows.Count).End(xlUp).Row)
      .AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("N1:N2"), Unique:=False
      .Offset(1).EntireRow.Delete
      If .Parent.FilterMode Then .Parent.ShowAllData
    End With
    Range("N2").ClearContents
    Application.ScreenUpdating = True
  End If
End Sub

Hello sorry to wake this up

But is there a way to set date range?

Say 11-09-18 to 19-09-18 with the InputBox maybe separated by comma ?


Thanks

 

[Peter_SSs]

But is there a way to set date range?

Say 11-09-18 to 19-09-18 with the InputBox maybe separated by comma ?

So, just keep rows that have a date anywhere in the entered date range?

 

[kelly mort]

Yes

 

[Peter_SSs]

Yes

The success/failure of this might depend on what is in the columns in between but see how it goes.

Sub Del_Rows_v3()
  Dim Resp As String
  Dim vDates As Variant
  
  Resp = InputBox("Enter date range, separated by a comma")
  If InStr(1, Resp, ",") Then
    vDates = Split(Resp, ",")
    If IsDate(vDates(0)) And IsDate(vDates(1)) Then
      Application.ScreenUpdating = False
      Range("N2").Formula = "=COUNTIFS(G4:M4,"">=""&" & CLng(DateValue(vDates(0))) & ",G4:M4,""<=""&" & CLng(DateValue(vDates(1))) & ")=0"
      With Range("A3:M" & Range("A" & Rows.Count).End(xlUp).Row)
        .AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("N1:N2"), Unique:=False
        .Offset(1).EntireRow.Delete
        If .Parent.FilterMode Then .Parent.ShowAllData
      End With
      Range("N2").ClearContents
      Application.ScreenUpdating = True
    End If
  End If
End Sub

 

[kelly mort]

Nice nice!!!

It's working fine.

Thanks

 

[Peter_SSs]

Great. Thanks for the follow-up.