Adding a row after last instances of dates

saracat2012

New Member
Joined
Jun 27, 2023
Messages
11
Office Version
  1. 365
Platform
  1. Windows
Hello again all. My brain hurts trying to figure out this one. I have a spreadsheet of all open orders, listed by date. I'm trying to find the last instance of a workday date, insert a row, format the row, and add text to the first cell. I can get the formatting part pretty easily, the tricky bit is finding the last instance of the workday and inserting the row. Attached is a before and after shot of what I'm trying to do. (I already have a macro that conditionally formats based on today's date and the workday function color in the dates.)

cards.xlsx
ABC
1DateProduct
27/1/2023Prod 1
37/5/2023Prod 2
47/20/2023Prod 5
57/21/2023Prod 3
67/24/2023Prod 7
77/24/2023Prod 4
87/25/2023Prod 8
97/26/2023Prod 100
107/26/2023Prod 10
117/27/2023Prod 42
127/28/2023Prod 937
137/31/2023
148/1/2023
15
16DateProduct
177/1/2023Prod 1
187/5/2023Prod 2
197/20/2023Prod 5
207/21/2023Prod 3
21Due Today/Overdue
227/24/2023Prod 7
237/24/2023Prod 4
24Day 1
257/25/2023Prod 8
26Day 2
277/26/2023Prod 100
287/26/2023Prod 10
29Day 3
307/27/2023Prod 42
31Day 4
327/28/2023Prod 937
33Day 5
347/31/2023
358/1/2023
Sheet2
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
test1.xlsb
ABCDEFGH
1DateProduct255DateProduct
27/1/2023Prod 149407Due Today/Overdue7/1/2023Prod 1
37/5/2023Prod 265535Day 17/5/2023Prod 2
47/20/2023Prod 55296274Day 27/20/2023Prod 5
57/21/2023Prod 312611584Day 37/21/2023Prod 3
67/24/2023Prod 710498160Day 47/24/2023Prod 4
77/24/2023Prod 416777215Day 57/25/2023Prod 8
87/25/2023Prod 8 7/5/2023Prod 2
97/26/2023Prod 1007/20/2023Prod 5
107/26/2023Prod 107/24/2023Prod 7
117/27/2023Prod 427/24/2023Prod 4
127/28/2023Prod 9377/25/2023Prod 8
137/31/20237/26/2023Prod 100
148/1/20237/26/2023Prod 10
157/24/2023Prod 77/27/2023Prod 42
167/24/2023Prod 47/28/2023Prod 937
177/25/2023Prod 8 7/31/2023
187/26/2023Prod 1007/5/2023Prod 2
197/26/2023Prod 107/24/2023Prod 7
207/27/2023Prod 427/24/2023Prod 4
217/28/2023Prod 9377/25/2023Prod 8
227/1/2023Prod 17/26/2023Prod 100
237/5/2023Prod 27/26/2023Prod 10
247/20/2023Prod 57/27/2023Prod 42
257/21/2023Prod 37/28/2023Prod 937
267/24/2023Prod 77/31/2023
277/24/2023Prod 4Due Today/Overdue
287/25/2023Prod 8 7/24/2023Prod 7
297/26/2023Prod 1007/24/2023Prod 4
307/26/2023Prod 107/24/2023Prod 7
317/27/2023Prod 427/1/2023Prod 1
327/28/2023Prod 9377/1/2023Prod 1
337/31/20237/20/2023Prod 5
348/1/20237/21/2023Prod 3
357/24/2023Prod 7Day 1
367/24/2023Prod 47/25/2023Prod 8
377/25/2023Prod 8 7/26/2023Prod 100
387/26/2023Prod 1007/21/2023Prod 3
397/26/2023Prod 107/24/2023Prod 4
407/27/2023Prod 427/25/2023Prod 8
417/28/2023Prod 9377/26/2023Prod 100
427/20/2023Prod 57/28/2023Prod 937
437/21/2023Prod 37/20/2023Prod 5
447/24/2023Prod 77/21/2023Prod 3
457/24/2023Prod 47/24/2023Prod 7
467/25/2023Prod 8 7/24/2023Prod 4
477/26/2023Prod 1007/25/2023Prod 8
487/26/2023Prod 10Day 2
497/27/2023Prod 427/26/2023Prod 100
507/28/2023Prod 9377/26/2023Prod 10
517/31/20237/26/2023Prod 10
528/1/20237/27/2023Prod 42
537/24/2023Prod 77/28/2023Prod 937
547/24/2023Prod 48/1/2023
557/25/2023Prod 8 7/28/2023Prod 937
567/26/2023Prod 1007/31/2023
577/26/2023Prod 108/1/2023
587/27/2023Prod 427/26/2023Prod 10
597/28/2023Prod 9377/27/2023Prod 42
607/1/2023Prod 17/28/2023Prod 937
617/5/2023Prod 2Day 3
627/20/2023Prod 57/27/2023Prod 42
637/21/2023Prod 37/24/2023Prod 7
647/24/2023Prod 77/24/2023Prod 7
657/24/2023Prod 47/24/2023Prod 4
667/25/2023Prod 8 7/25/2023Prod 8
677/26/2023Prod 1007/26/2023Prod 100
687/26/2023Prod 10Day 4
697/27/2023Prod 427/28/2023Prod 937
707/28/2023Prod 9377/26/2023Prod 10
717/31/20237/26/2023Prod 100
728/1/20237/26/2023Prod 10
737/24/2023Prod 77/27/2023Prod 42
747/24/2023Prod 48/1/2023
757/25/2023Prod 8 7/24/2023Prod 7
767/26/2023Prod 1007/24/2023Prod 4
777/26/2023Prod 107/25/2023Prod 8
787/27/2023Prod 427/26/2023Prod 100
797/28/2023Prod 9377/26/2023Prod 10
807/27/2023Prod 42
817/28/2023Prod 937
82Day 5
837/31/2023
848/1/2023
857/27/2023Prod 42
Sheet1


Result in Column G:H,

You need to copy column D:E (for internal dictionary to detect)
1689943426773.png


Once you've finished paste into column D:E, you may run the code ( right click sheet1 -> view code -> Paste below code) and run by clicking f5

VBA Code:
Option Explicit
Sub test()
application.screenupdating = false
Dim ws As Worksheet
'Dim dict As New Dictionary
Dim i%, a As Variant, k%, m%
Dim ss As Range
Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")
Set ws = Sheets("sheet1") 'May change into your desire sheet
ReDim b(1 To 5000, 1 To 4)
m = 1
'Store recorded D:E Columns into array
With ws
    a = .Range("d1:e" & .Cells(Rows.Count, "d").End(xlUp).Row).Value
    [g1:h100000].Clear 'Clear values and format
    [g1].Value = "Date"
    [h1].Value = "Product"
End With

For i = 1 To UBound(a, 1)
    If Not dict.Exists(a(i, 1)) Then 'If Red color not existing then
            k = k + 1
            dict.Add a(i, 1), a(i, 2) 'Add Red into Dict, Item = Day
            If m <> 1 Then 'For Item due today / Day
                m = m + 1
                Cells(m, "g").Value = dict.Item(a(i, 1))
                Cells(m, "g").Interior.Color = vbBlack
                Cells(m, "g").Font.Color = vbWhite
            End If
            For Each ss In Range("a2:a" & Cells(Rows.Count, "a").End(xlUp).Row) 'Loop through ranges check red color
                If dict.Exists(ss.Interior.Color) Then 'If range got red color
                    'lrow = Cells(Rows.Count, "J").End(xlUp).Row
                    m = m + 1
                    Range("A" & ss.Row & ":B" & ss.Row).Copy Cells(m, "g") 'Copy values to G
               End If
            Next ss
    dict.RemoveAll 'after finished remove dictionary red then record 2nd one
    End If
Next i

application.screenupdating = true

End Sub
 
Last edited:
Upvote 0
Solution

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

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
Back
Top