CODEWITHSUNDEEP
vbaexceldelete-row
David Van der Vieren
David Van der Vieren

Reputation: 275

Delete Row if date has already passed but not if the cell is blank VBA

I am trying to delete a row only if there is a date that is past today, but not if it is in the future or blank. Right now I am having a hard time getting the code to not delete the row if there is no blank because I want to keep the rows with no dates.

Sub deleteSpudedWells()
Dim lastRow As Integer
Dim firstRow As Integer
Dim ctr As Integer

Dim currentCell As Range
Dim valueOfDColumn
Dim NoNSpudedWells As Boolean



 lastRow = 300
 firstRow = 10

 Application.ScreenUpdating = False
 With Sheets("NDIC Renewals")
     For ctr = lastRow To firstRow Step -1
         Set currentCell = .Cells(ctr, 4)
         valueOfDColumn = currentCell.Value
         NoNSpudedWells = valueOfDColumn >= Date


         If Not NoNSpudedWells Then
             Debug.Print "deleting row of cell " + currentCell.Address
             currentCell.EntireRow.Delete
         End If
     Next
 End With
 Application.ScreenUpdating = True
 End Sub

Upvotes: 0

Views: 1191

Answers (3)

David Van der Vieren
David Van der Vieren

Reputation: 275

Thanks to BonCodigo It gave me an idea and i was able to adjuct the code to work. Needed to read this to make the code run properly valueOfDColumn = ""

 Sub deleteSpudedWells()
     Dim lastRow As Integer
     Dim firstRow As Integer
     Dim ctr As Integer

     Dim currentCell As Range
     Dim valueOfDColumn
     Dim NoNSpudedWells As Boolean

     lastRow = 300
     firstRow = 10

     Application.ScreenUpdating = False
     With Sheets("NDIC Renewals")
         For ctr = lastRow To firstRow Step -1
             Set currentCell = .Cells(ctr, 4)
             valueOfDColumn = currentCell.Value
             NoNSpudedWells = valueOfDColumn >= Date Or valueOfDColumn = ""

             If Not NoNSpudedWells Then
                 Debug.Print "deleting row of cell " + currentCell.Address
                 currentCell.EntireRow.Delete
             End If
         Next
     End With
     Application.ScreenUpdating = True
 End Sub

Upvotes: 1

bonCodigo
bonCodigo

Reputation: 14361

Try this out:

    For ctr = lastRow To firstRow Step -1
         Set currentCell = .Cells(ctr, 4)
         valueOfDColumn = currentCell.Value               

         If NOT (valueOfDColumn >= Date or currentCell.Value = "") Then
             Debug.Print "deleting row of cell " + currentCell.Address
             currentCell.EntireRow.Delete
         End If
     Next

BTW It's best to declare your valueOfDColumn as Date instead of leaving it to be a default variant :)

Upvotes: 1

Daniel
Daniel

Reputation: 13122

Add another condition to your if: 

If Not NoNSpudedWells AND valueOfDColumn <> 0 Then

If currentCell is blank, it's value will = 0.

Upvotes: 1

Related Questions