Delete rows based on date

jackjoush
NewLounger
Posts: 19
Joined: 25 Mar 2021, 21:33

Delete rows based on date

Post by jackjoush »

Hello everyone
This code below working well to delete any rows that contain a date that is older than from the current month

Code: Select all

Option Explicit
Sub test()
    Rows("1:2").Insert
    [j2].Formula = "=j4<=eomonth(today(),-1)"
    With Range("j3", Range("j" & Rows.Count).End(xlUp))
        .AdvancedFilter 1, [j1:j2]
        .Offset(1).EntireRow.Delete
        .Parent.ShowAllData
    End With
    Rows("1:2").Delete
    With Cells(1).CurrentRegion
        With .Offset(1).Resize(.Rows.Count - 1).Columns(1)
            .Value = Evaluate("row(1:" & .Rows.Count & ")")
        End With
    End With
End Sub
The problem right now is How I can transfer these rows to another sheet Let it be the result sheet ... Header in J1, data string starts j2 down
Here is what I currently have in my Module ... Thanks in advance.
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Delete rows based on date

Post by HansV »

Does this do what you want?

Code: Select all

Sub test()
    Dim w As Worksheet
    Set w = Worksheets("result")
    [1:2].Insert
    [j2].Formula = "=j4<=eomonth(today(),-1)"
    With Range("j3", Range("j" & Rows.Count).End(xlUp))
        .AdvancedFilter 1, [j1:j2]
        .Offset(1).EntireRow.Copy w.Range("A" & w.Range("j" & w.Rows.Count).End(xlUp).Row + 1)
        .Offset(1).EntireRow.Delete
        .Parent.ShowAllData
    End With
    [1:2].Delete
    With Cells(1).CurrentRegion
        With .Offset(1).Resize(.Rows.Count - 1).Columns(1)
            .Value = Evaluate("row(1:" & .Rows.Count & ")")
        End With
    End With
End Sub
Best wishes,
Hans

jackjoush
NewLounger
Posts: 19
Joined: 25 Mar 2021, 21:33

Re: Delete rows based on date

Post by jackjoush »

That's perfect Mr. Hans
Just one last point .. How can re-sequence data in the result sheet?
Again, thanks so much

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Delete rows based on date

Post by HansV »

That's trivial: add code like the code you already have, but for w (the result sheet):

Code: Select all

    With w.Cells(1).CurrentRegion
        With .Offset(1).Resize(.Rows.Count - 1).Columns(1)
            .Value = Evaluate("row(1:" & .Rows.Count & ")")
        End With
    End With
Best wishes,
Hans

jackjoush
NewLounger
Posts: 19
Joined: 25 Mar 2021, 21:33

Re: Delete rows based on date

Post by jackjoush »

That's perfect and very useful for me