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​

 

[Crystalyzer]

This gets you MOST of the way there. the code below will retrieve the data you want and put it in a msgbox, however, the msgbox has a limitation and the data wraps. Also, I'm not sure what would happen if you had more rows that could fit. There might be a character limit to the msgbox. I am looking into using a listbox on a user form as the "pop up" but thought I would give you what I have so far.

Sub ShowDateData()
    Dim arr() As Variant
    Dim arr2() As Variant
    Dim rng As Range
    Dim lRow As Long, i As Long, j As Long, k As Long, arr2Rows As Long
    Dim ws As Worksheet
    Dim strMsg As String, strTmp As String
    Dim uf As UserForm
    
    Set ws = ActiveSheet
    k = 0
    
    With ws
        lRow = .Cells(.Rows.Count, "M").End(xlUp).Row
        arr2Rows = WorksheetFunction.CountA(.Range("M:M"))
        arr = .Range("A1:M" & 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
    
    For i = 0 To UBound(arr2, 1)
        For j = 0 To UBound(arr2, 2)
            If arr2(i, j) <> "" Then
                strTmp = strTmp & arr2(i, j) & vbTab
            End If
        Next j
        If i = 0 Then
            strMsg = strTmp
        Else
            strMsg = strMsg & vbCrLf & strTmp
        End If
        strTmp = ""
    Next i
    
    MsgBox strMsg
End Sub

 

[Mussa]

the msgbox has a limitation and the data wraps

what you mean ? does the code crashes if I have a big data ?

I tested your code but two COLS are not arranged (NOTE & DATE) should show next to COL AMOUNT
see the picture


View attachment 44353

 

[Mussa]

 

[Crystalyzer]

That's what I meant about the msgbox having limitations. It wraps the text and cannot extend any wider.

 

[Mussa]

so any suggestion to fix this problem?

 

[Crystalyzer]

I've been looking into a user form with a listbox but I haven't gotten very far.

 

[Crystalyzer]

I think I solved the issue, however, I need to know if there is data in columns G through L and if you want that data included or not.

 

[Crystalyzer]

 

[Crystalyzer]