• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need vba code to delete orders that have a future date

Manny

New Member
Hi all,
I am seeking help with VBA. I have a data sheet with orders that may have multiple dates, and some of those dates may be future dates. If the order has a future date in any of the lines, then I need to remove the order entirely from the data set. For example, below are two orders, 135791 & 2468100. Order 135791 has three lines and it has a future date, which is on line 2, and since this order has a future date, then I would like to remove the order entirely from the data set. Please note, the future date may be in any of the lines, so regardless of what line the future date it is on, I need to remove that order. My data sheet may have thousands of orders, and I need to be able to identify which orders have a future date and delete them. Can you please assist?

1357911 1/1/2019
1357911 11/30/2020
1357911 10/12/2018
2468100 2/1/2017
2468100 3/1/2020
 

Logit

Active Member
.
Code:
Sub DelRow()
Dim i As Long
  
For i = Cells(Rows.Count, "B").End(xlUp).Row To 2 Step -1
    If CDate(Range("B" & i).Value) > Date Then
        Rows(i).EntireRow.Delete xlShiftUp
    End If
Next i

End Sub
 

AlanSidman

Well-Known Member
For your information only, an alternative solution is to use Power Query. Mcode follows

Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", type date}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Column2] < Date)
in
    #"Filtered Rows"
 

Attachments

Last edited:
Top