I am working on a spreadsheet that has data I need to remove based on three different cells. Here is my goal:
If A1=31 AND B2 contains a date > than the date in C2, delete the entire row. I need to loop through the full data set and check each row. I have it removing the row if A1=31 and looping through the worksheet, but can’t seem to add the date component.
Here is my coding. It is probably kind of messy since mostly copied and pasted. I am not an a very experienced coder and don’t really care what it looks like as long as it works. I am also not working with massive datasets so speed isn’t an issue.
Sub Remove_Future_Dates() Dim Firstrow As Long Dim LastRow As Long Dim Lrow As Long Dim CalcMode As Long Dim ViewMode As Long
Firstrow = ThisWorkbook.Worksheets(2).UsedRange.Cells(1).Row LastRow = ThisWorkbook.Worksheets(2).Cells(ThisWorkbook.Worksheets(2).Rows.Count, “A”).End(xlUp).Row
With Application CalcMode = .Calculation .Calculation = xlCalculationManual '.ScreenUpdating = False End With With ActiveSheet .Select Firstrow = .UsedRange.Cells(1).Row LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row For Lrow = LastRow To Firstrow Step -1 With .Cells(Lrow, "A") If Not IsError(.Value) Then If .Value = "31" Then .EntireRow.Delete End If End With Next Lrow End With
If A2= 31
B2 = 5/1/19
C2 = 4/1/19
The macro would remove the entire row since A2=31 and the date in B2 is greater than 4/1/19.