Excel- VBA help

 

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

End Sub

 

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.

By: Travis

Leave a Reply

Your email address will not be published. Required fields are marked *