Date based row deletion

[chirayu]

Hi Guys,

Basically I'm trying to speed up my code so need a faster way to delete rows.

Essentially the date criteria I use is anything that is less than "Date - 31"

Note that I am referencing dates starting cell AG6

Also note that since not every cell has a date I use column A as the IsEmpty() check for the Do Until loop

'Delete rows less than Today minus 31
Range("AG6").Select
Application.ScreenUpdating = False
Do Until IsEmpty(Range("A" & Activecell.Row))
    If Activecell <> "" And Activecell < (Date - 31) Then
        Activecell.EntireRow.Delete
    Else
        Activecell.Offset(1, 0).Select
    End If
Loop
Application.ScreenUpdating = True

----------------------------------------------------------------------------------------
Thread moved to appropriate forum. - Chihiro

 

[Chihiro]

There are few ways. Instead of looping and deleting row by row.

1. Use autofilter on range. Display only those that should be deleted. Then delete visible range in one shot.

2. Use advanced filter to extract data you want to keep. Clear contents of original range. Paste back extracted data.

3. Use Collection/Dictionary in code. First load range to variant array. Then loop through, when condition is met for keeping data, move it into collection/dictionary. You can then clear range, either load data by looping through collection/dictionary, or put it into array and load back in one shot.

1 is fairly easy to code, just record macro for manual process of filtering on date column. You'll have base syntax.

2 is easiest to maintain, but you'll need additional sheet for criteria and extraction range.

3 is fastest. But will need bit more coding than above two.

Though I'd not recommend it, another method is to union cells that should be deleted. Then delete entire row of union range.

If you need specific help, upload sample.

 

[Marc L]

Hi !

First, with posting in this section instead of the VBA one ?!

Basically I'm trying to speed up my code so need a faster way to delete rows.

Using a loop to delete row by row is often the slowest !
One of the faster way for big / huge data is to use the beginner way
like when operating manually :
• a helper column to mark rows to delete (TRUE)
or to keep (FALSE) via a formula for example,
• a sort on this helper column
• a global Clear of rows at once, that's it !

As you can already see in some threads of the VBA section …
​

 

[chirayu]

Sorry Marc. Was reading the Vault and posted in the wrong section. can you move it over please.

 

[Marc L]

Already done by Chihiro …

As written in post #3, samples of the beginner way
according to the TEBV main rule,
first one with a formula for the helper column
and the second by checking an array :

Delete Data (where you wrote a post !)

Filter and Delete - Date to be taken from another sheet …
​

 

[chirayu]

Thanks. Also I was thinking. I've seen some people use a For Loop e.g

FOR LR to Step-2" but I've never done that so no clue how I'd use it.

Also sample file attached

 

[Marc L]

As using a loop to delete row by row may be far slower with big data
in particular when using Select (as a good code not uses it) …

I remember a loop procedure lasting more than ten minutes
and with the beginner way that needed less than one even manually ‼

When a very very beginner uses the bad slow Select within a loop
so at least he must desactivate the display during the procedure
(see ScreenUpdating in the VBA help and within my samples) …

FOR LR to Step-2

Wow ‼ First read how to use a For … Next loop in the VBA help !
To see also in my second link or in the next posts samples …
​

 

[Chihiro]

Here's sample using array and collection. Adjust DateDiff as needed.

Sub Demo()
Dim ar, x
Dim coll As New Collection
With Sheet1
    ar = .Range("A6:AG" & .Cells(Rows.Count, 1).End(xlUp).Row).Value
    .Range("A5").CurrentRegion.Offset(1).ClearContents

    For i = 1 To UBound(ar)
        If Len(ar(i, 33)) = 0 Or DateDiff("d", ar(i, 33), Date) <= 31 Then
            coll.Add Application.Index(ar, i, 0)
        End If
    Next
    For i = 1 To coll.Count
        .Cells(5 + i, 1).Resize(, UBound(coll(i))) = coll(i)
    Next
End With
End Sub

 

[Marc L]

I've seen some people use a For Loop e.g

FOR LR to Step-2" but I've never done that so no clue how I'd use it.

According to your original code and your attachment
as a very beginner starter (which I shouldn't use for big data
but not a concern here with the few rows of your sample) :​

Sub Demo0()
  Const C = 33
    Dim D&, V, R&
        D = Date - 31
        Application.ScreenUpdating = False
With Sheet1.UsedRange.Rows
        V = .Columns(1).Value2
    For R = .Count To 2 Step -1
        If IsDate(.Cells(R, C).Value) Then If .Cells(R, C).Value2 < D Then .Item(R).Delete
    Next
       .Columns(1).Value2 = V
End With
        Application.ScreenUpdating = True
End Sub

Do you like it ? So thanks to click on bottom right Like !
​