Trouble with Macro

[jpencek3]

I have a data set where the number of rows will vary month. There are different dates in columns E and J. I'm looking for a macro that deletes all rows that have any date in column E (half or so will have dates and the rest will be blank). Then after that will delete all the rows that have a date where the day of the month in column J is later than the current day. For example row 4 has no date in column E but a date of 8/24/18 in column J. It will be deleted because the 24th is later in the month than the 17th (today).

I feel like I'm going about this very inefficiently by trying to get a macro insert a new column A then use a function like =if(B1="","Remove,IF(F1<>"","Remove",IF(DAY(TODAY())<DAY(K1),"Remove","Investigate"))). I'm still running into issues sorting that column then deleting all the "Remove" rows

Can someone please help?

 

[Logit]

.
The following method (most likely just one of many ways to accomplish your goal), utilizes a "helper column" (Col K). This column can be hidden if desired.
Also, the "helper column" can be any column you like, it doesn't have to be "K".

I have broken down the macro into three small sections so hopefully it will be easier to understand. Macro "Replace Zero", uses the helper column ("K") to establish the day number of the week,
which is later used in the macro "DelRowsDaynDates2" to determine which rows should be deleted based on the dates in Column J.

The process then goes to macro "DelRowsDaynDates", which determines which rows in Column E have a date and then delete that row.

Then the process goes to macro "DelRowsDaynDates2", which determines which rows in Column J should be deleted based on the Day of the Week Number.

Option Explicit


Sub Replace_Zero()
  With Range("J2", Range("J" & Rows.Count).End(xlUp))
    .Offset(0, 1).Value = "=DAY(J2)"
    '.Value = .Value
  End With
  DelRowsDaynDates
End Sub




Sub DelRowsDaynDates()
Dim lr As Long, i As Long
lr = Range("E" & Rows.Count).End(xlUp).Row
    For i = lr To 2 Step -1
        If Range("E" & i) <> "" Then
            With Range("E" & i)
                .EntireRow.Delete
            End With
        End If
    Next i
DelRowsDaynDates2
End Sub




Sub DelRowsDaynDates2()
Dim lr As Long, i As Long
Dim strDate


strDate = Now()




lr = Range("K" & Rows.Count).End(xlUp).Row
    For i = lr To 2 Step -1
        If Range("K" & i) > Day(strDate) Then
            With Range("J" & i)
                .EntireRow.Delete
            End With
        End If
    Next i
End Sub

You can download a copy of the working workbook here : https://www.amazon.com/clouddrive/share/f3LrAlYqKFlWLHdXYwLq3hO2rPEIPuXAD0rLduwzpLe

 

[Fluff]

Another option

Sub DelRwsOnDate()
   Range("E:E").SpecialCells(xlConstants).EntireRow.Delete
   With Range("J2", Range("J" & Rows.Count).End(xlUp))
      .Value = Evaluate(Replace("if(day(@)>day(today()),"""",@)", "@", .Address))
      .SpecialCells(xlBlanks).EntireRow.Delete
   End With
End Sub

 

[jpencek3]

Thank you both for your replies! This macro is working perfect, however I've run into one issue... Any time column E is completely empty (no dates at all) it doesn't seem to work. How can I fix?

 

[Logit]

.
Were you speaking of my submission ? I've ran it here and it works. ??

 

[Fluff]

If you were referring to my code try

Sub DelRwsOnDate()
   On Error Resume Next
   Range("E:E").SpecialCells(xlConstants).EntireRow.Delete
   On Error GoTo 0
   With Range("J2", Range("J" & Rows.Count).End(xlUp))
      .Value = Evaluate(Replace("if(day(@)>day(today()),"""",@)", "@", .Address))
      .SpecialCells(xlBlanks).EntireRow.Delete
   End With
End Sub

 

[jpencek3]

This works exactly like I wanted, thank you so much!! Unfortunately, I just found out we cannot just filter by those items and need to make some changes. Can you help me with this part too? Here is what it needs to do

1. Delete any row that has a date in columns E, J, and O (while keeping the headers in row 1)
2. Delete any row that has no date in column E, any date in column O, and a date where the day the day of the month in column J is later than the current day
3. Insert a new column D labeled "Comments"

So basically it is the same as before, except the ones that have any date in column E also need columns J and O to be non-blank as well, and we need to keep the headers in row 1. The second part is the same with the date for column J, except column O needs to be non-blank as well. And then after all the rows are filtered out, we need to insert a column D labeled Comments.

The original request was over my head, so these additions are getting way over my head. I'd really appreciate the help!

 

[Fluff]

As this is now a completely different question, can you please start a new thread.