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
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,956
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
No replace literally all the Clng part with Clng(Date), none of the worksheet/workday part is needed as that is to define the working day which you don't need if you just want the current day.
 
Last edited:

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Watch MrExcel Video

Forum statistics

Threads
1,126,946
Messages
5,621,770
Members
415,856
Latest member
jimb2k

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