VBA delete rows 2 or more days after a certain date

[background]

This problem is driving me insane

I have thousands of lines of data, and in columns H and I, there are dates. I want my macro to go through and delete all rows where the date in column I is 2 or more days after the date in column H. The number of lines depends on the data I download, so I should Dim a header row and the bottom row as integers. Could someone please help me find a solution to this bloody problem? Thank you!

 

[Siddharth Rout]

@Siddharth Rout it’s very slightly different . My helper column is getting done in memory based on the reference to “thousands of lines”, I also wanted to sort to consolidate the rows to be deleted for the same reason.
Wish my code looked as clean as yours though

For heavy calculations, I also prefer using arrays but this was a pretty straight forward requirement so did not use it.

As far as the "clean" code is concerned, I always ensure that I indent (using MZ Tools), comment and test (sometimes not possible to test and I do mention that) the code before posting. It's a habit that I developed over the last so many years

 

[background]

I would do this slightly in a different way. I will let Excel do all the dirty work.

LOGIC

1. Insert a helper column in Col J
2. Insert the DATEDIF formula to calculate date difference
3. Filter the helper column on date difference >=2
4. Delete the relevant rows
5. Finally delete the helper column

CODE

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim lRow As Long
    Dim delRange As Range
  
    '~~> Change this to the relevant sheet
    Set ws = ThisWorkbook.Sheets("Sheet1")
  
    With ws
        .AutoFilterMode = False
      
        '~~> Find last row in Col H
        lRow = .Range("H" & .Rows.Count).End(xlUp).Row
      
        '~~> Insert a helper column in col J
        .Columns(10).Insert Shift:=xlToRight
      
        '~~> Add header
        .Range("J1").Value = "TempHeader"
      
        '~~> Insert the DatedIf formula in all cells in 1 go
        .Range("J2:J" & lRow).Formula = "=DATEDIF(H2,I2,""d"")"
      
        '~~> Filter on date >=2
        With .Range("J1:J" & lRow)
            .AutoFilter Field:=1, Criteria1:=">=2"
          
            Set delRange = .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow
        End With
      
        '~~> Delete the rows in 1 go if applicable
        If Not delRange Is Nothing Then delRange.Delete
      
        '~~> Delete the helper column
        .Columns(10).Delete
    End With
End Sub

SCREENSHOT

View attachment 56040

Thank you very much @Siddharth Rout , I just seem to have run into a syntax error on the line .Range("J2:J" & lRow).Formula = "=DATEDIF(H2,I2,""d"")". Could you help me please?

@Alex Blakenburg sorry, the data is on another computer and is rather sensitive, but the screenshot @Siddharth Rout sent above is an accurate representation.

Both, thank you very much for your help, it is much appreciated, and apologies for my clear lack of knowledge on the subject!

 

[Siddharth Rout]

Thank you very much @Siddharth Rout , I just seem to have run into a syntax error on the line .Range("J2:J" & lRow).Formula = "=DATEDIF(H2,I2,""d"")". Could you help me please?

@Alex Blakenburg sorry, the data is on another computer and is rather sensitive, but the screenshot @Siddharth Rout sent above is an accurate representation.

Both, thank you very much for your help, it is much appreciated, and apologies for my clear lack of knowledge on the subject!

The code that I posted is tried and tested... Did you make any changes to the code that I posted?

 

[Joe4]

Thank you very much @Siddharth Rout , I just seem to have run into a syntax error on the line .Range("J2:J" & lRow).Formula = "=DATEDIF(H2,I2,""d"")". Could you help me please?

@Alex Blakenburg sorry, the data is on another computer and is rather sensitive, but the screenshot @Siddharth Rout sent above is an accurate representation.

Both, thank you very much for your help, it is much appreciated, and apologies for my clear lack of knowledge on the subject!

I think you are seeing why I asked for sample data. Otherwise, we are making assumptions and testing it out on our own data that we came up with and not your actual data.
So we really aren't testing the same thing. Sometimes, some very subtle differences in the data and structure can make all the difference.

 

[Alex Blakenburg]

@Siddharth Rout , I just seem to have run into a syntax error on the line .Range("J2:J" & lRow).Formula = "=DATEDIF(H2,I2,""d"")". Could you help me please?

As Siddharth suggested this can only be caused by having changed his code.
You may be able to get away with just posting your whole Sub so one of us can check / run it and see what you changed.
Please use the VBA button to post the code.

Thanks. I've tried running it, but have got a mismatch on the line:

If arrSrc(i, 9) - arrSrc(i, 8) >= dayLimit Then. Could you help me out please?

This will happen if you have any text or a formula returning "" in either Column H or I.
Note: something that looks like a date could still be text. ( you can check this by picking a different date format and if nothing changes it is text or use in isnumber or istext formula check)

In my code as it stands it will error out. Ideally you need to fix the actual data. Alternatively you need to tell me whether you want line with text deleted or not.
@Siddharth Rout's code will not error out. The help column will produce an error and the filter will ignore the errors, so lines with errors will not be deleted.

In terms of confidentiallity you can delete all the data except columns H & I, we do need to see the all Column headings and the Row and Column references. Also we would prefer an XL2BB so we can actually paste the data into our test workbook. We only need a sample of rows (say 15-20) but it should include some rows with the day in the month being >= 13.