pop up message all of data contain date when open file

[Mussa]

hi
I search for macro when open file . it pop up message show all of data based on col M contains date
so in my case it should show the rows 2,6,10

REP1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	ITEM	BATCH NO	CODE NO	NAME	AMOUNT	NOTES							DATE
2	1	BT-00	CD-00	ALI1	250	RECORDED							03/08/2021
3	2	BT-01	CD-01	ALI2	200	NOT RECORDED
4	3	BT-02	CD-02	ALI3	300	NOT RECORDED
5	4	BT-03	CD-03	ALI4	400	NOT RECORDED
6	5	BT-04	CD-04	ALI5	500	RECORDED							05/08/2021
7	6	BT-05	CD-05	ALI6	120	NOT RECORDED
8	7	BT-06	CD-06	ALI7	300	NOT RECORDED
9	8	BT-07	CD-07	ALI8	120	NOT RECORDED
10	9	BT-08	CD-08	ALI9	50	RECORDED							07/08/2021
11	10	BT-09	CD-09	ALI10	120	NOT RECORDED
1

the messgae should be like this

ITEM	BATCH NO	CODE NO	NAME	AMOUNT	NOTES	DATE
1	BT-00	CD-00	ALI1	250	RECORDED	03/08/2021​
2	BT-04	CD-04	ALI5	500	RECORDED	05/08/2021​
3	BT-08	CD-08	ALI9	50	RECORDED	07/08/2021​

 

[Mussa]

then debug and step through the code until you get the error

@Crystalyzer sorry I don't read wll and i fixed it and works well but when I change the date in col L it doesn't show in listbox despite of it I change theses

With ws
        lRow = .Cells(.Rows.Count, "L").End(xlUp).Row
        arr2Rows = WorksheetFunction.CountA(.Range("L:L"))
        arr = .Range("A1:L" & lRow)

but not succeddes . can you guide me what I have to change, please ?

 

[Crystalyzer]

so your data now looks like this?

Mussa 20210807.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	ITEM	BATCH NO	CODE NO	NAME	AMOUNT	NOTES						DATE
2	1	BT-00	CD-00	ALI1	250	RECORDED						8/3/2021
3	2	BT-01	CD-01	ALI2	200	NOT RECORDED
4	3	BT-02	CD-02	ALI3	300	NOT RECORDED						8/6/2021
5	4	BT-03	CD-03	ALI4	400	NOT RECORDED
6	5	BT-04	CD-04	ALI5	500	RECORDED						8/5/2021
7	6	BT-05	CD-05	ALI6	120	NOT RECORDED						5/1/2021
8	7	BT-06	CD-06	ALI7	300	NOT RECORDED
9	8	BT-07	CD-07	ALI8	120	NOT RECORDED
10	9	BT-08	CD-08	ALI9	50	RECORDED						8/7/2021
11	10	BT-09	CD-09	ALI10	120	NOT RECORDED
12
Sheet1

 

[Mussa]

yes

 

[Crystalyzer]

Private Sub CommandButton1_Click()
    Unload Me
End Sub

Private Sub UserForm_Initialize()
    Load UserForm1
    With ListBox1
        .ColumnCount = 13
        .ColumnWidths = "1 cm;2 cm;2 cm;2 cm;2 cm;3 cm;0;0;0;0;0;3 cm;0"
        .List = ShowDateData()
        .ListIndex = 0
    End With
    With UserForm1
        .StartUpPosition = 0
        .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
        .Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
    End With
End Sub

Private Function ShowDateData() As Variant
    Dim arr() As Variant
    Dim arr2() As Variant
    Dim lRow As Long, i As Long, j As Long, k As Long, arr2Rows As Long
    Dim ws As Worksheet
    
    Set ws = ActiveSheet
    k = 0
    
    With ws
        lRow = .Cells(.Rows.Count, "L").End(xlUp).Row
        arr2Rows = WorksheetFunction.CountA(.Range("L:L"))
        arr = .Range("A1:L" & lRow)
        ReDim arr2(arr2Rows - 1, UBound(arr, 2) - 1)
        For i = 1 To UBound(arr, 1)
            If arr(i, UBound(arr, 2)) <> "" Then
                For j = 0 To UBound(arr, 2) - 1
                    arr2(k, j) = arr(i, j + 1)
                Next
                k = k + 1
            End If
        Next i
    End With
        
    ShowDateData = arr2
End Function

 

[Mussa]

brilliant ! thanks so much for everything