VBA Code to Delete Rows based on Next Business Day

AlwaysLearning2018

Board Regular
Joined
Nov 23, 2018
Messages
51
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***FundCust #B/SID Orig. Face PrincipalProceedsPrice*StatusTrade Dt.Settle Dt.Type
1111111​
222222​
ABC
123456​
B65985145Y
25000​
25000​
25000​
100​
E
12/17/2019​
12/30/2019​
CORPB
1111112​
222223​
ABC
123457​
S65985145Y
300000​
296917.97​
297523.74​
100​
E
12/18/2019​
12/19/2019​
TREASURY
1111113​
222224​
ABC
123458​
S65985145Y
2800000​
2741156.25​
2751733.2​
100​
E
12/18/2019​
12/19/2019​
TREASURY
1111114​
222225​
ABC
123459​
B65985145Y
3000000​
3018452.85​
3019120.2​
100​
E
12/18/2019​
12/19/2019​
TREASURY
1111115​
222226​
ABC
123460​
S65985145Y
150000​
161250​
163883.33​
100​
E
12/18/2019​
12/20/2019​
CORPB
1111116​
222227​
ABC
123461​
B65985145Y
25000​
25968.75​
26245.23​
100​
E
12/18/2019​
12/20/2019​
CORPB
1111117​
222228​
ABC
123462​
B65985145Y
1000000​
985195.31​
986038.89​
100​
E
12/18/2019​
12/19/2019​
TREASURY
1111118​
222229​
ABC
123463​
B65985145Y
6000000​
5992265.63​
5993331.2​
100​
E
12/18/2019​
12/19/2019​
TREASURY
1111119​
222230​
ABC
123464​
B65985145Y
1000000​
984609.38​
986244​
100​
E
12/18/2019​
12/19/2019​
TREASURY
1111120​
222231​
ABC
123465​
B65985145Y
300000​
301511.72​
302177.24​
100​
E
12/18/2019​
12/19/2019​
TREASURY
1111121​
222232​
ABC
123466​
B65985145Y
1000000​
988906.25​
989684.94​
100​
E
12/18/2019​
12/19/2019​
TREASURY
1111122​
222233​
ABC
123467​
S65985145Y
1800000​
1727468.51​
1730414.3​
100​
E
12/18/2019​
12/19/2019​
MBS
1111123​
222234​
ABC
123468​
S65985145Y
1700000​
1597289.71​
1600014.7​
100​
E
12/18/2019​
12/19/2019​
MBS
1111124​
222235​
ABC
123469​
S65985145Y
5875000​
5058995.35​
5067535​
100​
E
12/18/2019​
12/19/2019​
MBS
1111125​
222236​
ABC
123470​
S65985145Y
1800000​
1791358.76​
1794016.2​
100​
E
12/18/2019​
12/19/2019​
MBS
1111126​
222237​
ABC
123471​
S65985145Y
1950000​
1585109.12​
1587442.3​
100​
E
12/18/2019​
12/19/2019​
MBS
1111127​
222238​
ABC
123472​
S65985145Y
1800000​
1437115.17​
1439530.5​
100​
E
12/18/2019​
12/19/2019​
MBS
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

hernantorres23

Active Member
Joined
Nov 21, 2019
Messages
269
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. Web
Tell me, based in your example data, which rows must I delete?
 

AlwaysLearning2018

Board Regular
Joined
Nov 23, 2018
Messages
51
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

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,989
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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

Board Regular
Joined
Nov 23, 2018
Messages
51

ADVERTISEMENT

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

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,989
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
If Settle Dt. Column is column M then try the code below on a copy of your data as we are deleting...

Code:
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

Board Regular
Joined
Nov 23, 2018
Messages
51

ADVERTISEMENT

Hi MARK858,

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

AlwaysLearning2018

Board Regular
Joined
Nov 23, 2018
Messages
51
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

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,989
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Not by a computer but try changing where you get the Clng part to just Clng(Date)
 

AlwaysLearning2018

Board Regular
Joined
Nov 23, 2018
Messages
51
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,696
Messages
5,626,361
Members
416,175
Latest member
LavendarRabbit

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top