0

I have the following VBA script, where I would like to filter based on the 3rd column - StartDate : 

Sub RemDates()
Dim RowToTest As Long
Dim ws As Worksheet

For Each ws In ThisWorkbook.Sheets
  For RowToTest = ws.Cells(Rows.Count, 3).End(xlUp).Row To 1 Step -1  
    With ws.Cells(RowToTest, 3)
      If .Value > #12/24/2016# _
        And .Value < #12/29/2016# Then _
          ws.Rows(RowToTest).EntireRow.Delete
    End With
  Next RowToTest
Next
End Sub

But this is not working correctly. The following is a screenshot of my spreadsheet (which I want to filter ) .

enter image description here

Any tips appreciated , thanks

Community
  • 1
  • 1
Caffeinated
  • 11,982
  • 40
  • 122
  • 216
  • 2
    What goes wrong? I am assuming this goes wrong because you loop through the rows and within that loop you delete rows, which harms the loop. – Hace Jan 05 '17 at 15:22
  • Are the values in the column stored as dates or text? – Preston Jan 05 '17 at 15:41
  • @tompreston - I think it's as "Custom" ( a CSV file ) - in excel when I right-click and choose "properties " that's what comes up – Caffeinated Jan 05 '17 at 15:42
  • Infact, I'm not sure what's wrong with the circled cell? Its value doesn't meet the criteria to be deleted, you may want to use an 'or' in place of the 'and'? – Preston Jan 05 '17 at 15:43
  • @tompreston - Apologies, I really just meant to indicate the whole column there. Nothing wrong with that specific cell – Caffeinated Jan 05 '17 at 15:44
  • @tompreston - fixed the pic – Caffeinated Jan 05 '17 at 15:46
  • 3
    Not a problem. Your code is saying if the value is between 12/24/2016 and 12/29/2016 then delete the row, is this definitly what you want? All the values in the column seem to be fine? – Preston Jan 05 '17 at 15:50
  • @tompreston - Actually the logic was reversed in my mind. I changed it to be `If .Value2 < #12/26/2016# _ Or .Value2 > #12/30/2016# _ Then _` , which gets the results I need. Thanks ! – Caffeinated Jan 05 '17 at 16:14

0 Answers0