VBA Code to Delete Rows based on Next Business Day

[AlwaysLearning2018]

Hi All,

I am working off a spreadsheet that contains the below data. I have code written to delete rows based on the criteria in Column N (Type) as shown below. The issue is there has been a change and I need to delete any rows in the column labeled Settle Dt that are not the next business day. So using today's date as an example, I want a macro to delete anything in the Settle Dt. Column that isn't Today's date + 1. Again, if it is a Friday I am looking for a way to push that to the next business day not the next date. I do not know how to write a vba code to account for this. Any help that anyone can give me would be much appreciated.

Thanks everyone!!

**Ticket #

Tk 2

***Fund

Cust #

B/S

ID

Orig. Face

Principal

Proceeds

Price

*Status

Trade Dt.

Settle Dt.

Type

1111111​

222222​

ABC

123456​

B

65985145Y

25000​

25000​

25000​

100​

E

12/17/2019​

12/30/2019​

CORPB

1111112​

222223​

ABC

123457​

S

65985145Y

300000​

296917.97​

297523.74​

100​

E

12/18/2019​

12/19/2019​

TREASURY

1111113​

222224​

ABC

123458​

S

65985145Y

2800000​

2741156.25​

2751733.2​

100​

E

12/18/2019​

12/19/2019​

TREASURY

1111114​

222225​

ABC

123459​

B

65985145Y

3000000​

3018452.85​

3019120.2​

100​

E

12/18/2019​

12/19/2019​

TREASURY

1111115​

222226​

ABC

123460​

S

65985145Y

150000​

161250​

163883.33​

100​

E

12/18/2019​

12/20/2019​

CORPB

1111116​

222227​

ABC

123461​

B

65985145Y

25000​

25968.75​

26245.23​

100​

E

12/18/2019​

12/20/2019​

CORPB

1111117​

222228​

ABC

123462​

B

65985145Y

1000000​

985195.31​

986038.89​

100​

E

12/18/2019​

12/19/2019​

TREASURY

1111118​

222229​

ABC

123463​

B

65985145Y

6000000​

5992265.63​

5993331.2​

100​

E

12/18/2019​

12/19/2019​

TREASURY

1111119​

222230​

ABC

123464​

B

65985145Y

1000000​

984609.38​

986244​

100​

E

12/18/2019​

12/19/2019​

TREASURY

1111120​

222231​

ABC

123465​

B

65985145Y

300000​

301511.72​

302177.24​

100​

E

12/18/2019​

12/19/2019​

TREASURY

1111121​

222232​

ABC

123466​

B

65985145Y

1000000​

988906.25​

989684.94​

100​

E

12/18/2019​

12/19/2019​

TREASURY

1111122​

222233​

ABC

123467​

S

65985145Y

1800000​

1727468.51​

1730414.3​

100​

E

12/18/2019​

12/19/2019​

MBS

1111123​

222234​

ABC

123468​

S

65985145Y

1700000​

1597289.71​

1600014.7​

100​

E

12/18/2019​

12/19/2019​

MBS

1111124​

222235​

ABC

123469​

S

65985145Y

5875000​

5058995.35​

5067535​

100​

E

12/18/2019​

12/19/2019​

MBS

1111125​

222236​

ABC

123470​

S

65985145Y

1800000​

1791358.76​

1794016.2​

100​

E

12/18/2019​

12/19/2019​

MBS

1111126​

222237​

ABC

123471​

S

65985145Y

1950000​

1585109.12​

1587442.3​

100​

E

12/18/2019​

12/19/2019​

MBS

1111127​

222238​

ABC

123472​

S

65985145Y

1800000​

1437115.17​

1439530.5​

100​

E

12/18/2019​

12/19/2019​

MBS

 

[hernantorres23]

Tell me, based in your example data, which rows must I delete?

 

[AlwaysLearning2018]

Hi hernantorres23,

I would want to delete those rows that do not contain 12/19/2019 in the Settle Dt. Column. So anything that is 1 business day greater than the current days date is the data I need remaining.

 

[MARK858]

I assume it is still 12/19/2019 where you are....

First of all you state

I would want to delete those rows that do not contain 12/19/2019

so 12/19/2019 would remain, then you state

So anything that is 1 business day greater than the current days date is the data I need remaining

The next business day would be 12/20/2019 so that is what you want remaining.

The 2 statements contradict each other if my assumption on your current date is correct, please can you clarify what is your current date, your criteria and what you want deleted based on your table.

 

[AlwaysLearning2018]

Sorry for the confusion. So on any given day I get a file that has a trade date and a settle date for each line item. Today is still 12/18 where I am located. All the line items that show 12/19 in the Settle Dt. Column is information that I DO NOT want to delete. I want to keep that info. Anything that is not 1 business day greater in the Settle Dt. Column than the date shown in the Trade Dt. Column I want to write vba to delete those rows. So on going tomorrow when I have items that show 12/19 in the Trade Dt. Column I want to delete any rows in which the Settle Dt. Column has a date other than 12/20. And I only want to account for business days not weekend days. I hope this makes sense.

Thank you very much for taking the time to respond.

 

[MARK858]

If Settle Dt. Column is column M then try the code below on a copy of your data as we are deleting...

Sub FilterDeleteNotNWD()
    With Range("M1:M" & Range("M" & Rows.Count).End(xlUp).Row)
        Application.ScreenUpdating = False
        .AutoFilter 1, "<>" & CLng(Application.WorksheetFunction.WorkDay(Date, 1))
        
        On Error Resume Next
        .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        On Error GoTo 0
        .AutoFilter
        Application.ScreenUpdating = True
    End With
End Sub

 

[AlwaysLearning2018]

Hi MARK858,

This worked PERFECTLY. Thank you very much for this. I appreciate it so much!!

 

[AlwaysLearning2018]

Hi MARK858,

Is there a way I can tweak the code you provided to me to delete rows that are not "today's date"? Looking for a way now to simply delete all rows where the date in a specific column (I'll use column M as in your example) is not the current day. Not sure if I would be able to use what you have already provided to me but make a few adjustments, or if I need to completely write a new vba code.

Thank you!!

 

[MARK858]

Not by a computer but try changing where you get the Clng part to just Clng(Date)

 

[AlwaysLearning2018]

Hi MARK858,

I changed .Auto filter 1, "<>" & CLng(Application.WorksheetFunction.WorkDay(Date, 1)) to just (Date)) at the end of the code. I am getting an error that highlights the .WorkDay portion that states Argument not optional. Any help you may be able to give me would be greatly appreciated.