how populate the last entered data in listbox when run userform

[abdelfattah]

hi
I would when run the userform I would show the last entered data .
in pic 1 when enter data from the first time

in pic2 enter new data
\

pic 3 it populate the last entered data with ignore the old data

this is the current code to show all of data

Private Sub UserForm_Initialize()
   Dim LastRow As Long
    LastRow = Range("A" & Rows.Count).End(xlUp).Row    '<---- ??????
    With ListBox1
        .ColumnCount = 6    '<---- Change to 18??????
        .ColumnWidths = "70;70;100;70;70;70"    '<---- 18 x each column width
        .List = Range("A1:F" & LastRow).Value    '<---- Change the "I" to the column number used above ( S = 18)
    End With
End Sub

thanks
and so on if I enter new data it will populate new data in listbox with ignores the old data
note: if you want depending on date to do that I don't mind . it's up to you how should do it.

 

[Sahak]

HI Abdel Fattah​

In your code change

.List = Range("A1:F" & LastRow).Value

to

.RowSource = "A1:F" & LastRow

it should be:

Private Sub UserForm_Initialize()
   Dim LastRow As Long
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    With ListBox1
        .ColumnCount = 6
        .ColumnWidths = "70;70;100;70;70;70"
        .RowSource = "A1:F" & LastRow
    End With
End Sub

 

[abdelfattah]

thanks but it doesn't make difference. it shows all of data .

 

[Sahak]

Sorry, I missed ignore the old data
I think you should
have a copy of entered a new data & get on user form from there
or
Somewhere, for example in Range("K1") enter the count of newly added rows (variable "LastAddedRows") then use this code

Private Sub UserForm_Initialize()
    Dim LastRow As Long
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    Dim LastAddedRows As Long
    LastAddedRows = Range("K1").Value
    With ListBox1
        .ColumnCount = 6
        .ColumnWidths = "70;70;100;70;70;70"
        .RowSource = ("A" & LastRow - LastAddedRows + 1) & ":F" & LastRow
    End With
End Sub

 

[abdelfattah]

thanks , but there is a problem if I have data are repetead contain the same date . it will take the last date for lastrow and ignore the rest of data contain the same date .

I would show all of data for the last date . even they are repeated . it shouldn't ignore the rest of repeated data contain the same date .

 

[Sahak]

Sorry, I was confused about what to do with dates, please show an example of what you need to see in the Listbox from the data

 

[abdelfattah]

see the last rows 7,8,9 theses the last entered with repeat the same date

CL.xlsx
	A	B	C	D	E	F
1	DATE	CODE	BRAND	TYPE	ORIGIN	QUANTITY
2	1/1/2021	AA	20-W50 208L	Q8	EU	100
3	1/2/2021	AA1	20-W50 12x1L	Q8	EU	101
4	1/3/2021	AA2	20-W50 15x1L	Q8	EU	102
5	1/4/2021	BB1	20-W50 208L	CAS	EU	103
6	1/5/2021	BB2	20-W50 12x1L	CAS	EU	104
7	1/6/2021	BB3	20-W50 15x1L	CAS	EU	105
8	1/6/2021	BB4	20-W50 15x1L	ENI	EU	106
9	1/6/2021	BB5	20-W50 15x1L	ENI	EU	107
SHEET1

the result should show like this

 

[Sahak]

Just making clear for me, can we say show the latest date data only, no matter how many records we have, if one record with the latest date, then show one line, if 5 records have the same latest date, then show 5 lines?

 

[abdelfattah]

Just making clear for me, can we say show the latest date data only, no matter how many records we have, if one record with the latest date, then show one line, if 5 records have the same latest date, then show 5 lines?

exactly this is what I want based on date .

 

[Sahak]

Here you go

Private Sub UserForm_Initialize()
    Dim LastRow As Long
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    Dim MaxDt, CntLatestDate As Long
    MaxDt = WorksheetFunction.Max(Range("A:A"))
    CntLatestDt = Application.WorksheetFunction.CountIf(Range("A:A"), MaxDt)
    With ListBox1
        .ColumnCount = 6
        .ColumnWidths = "70;70;100;70;70;70"
        .RowSource = ("A" & LastRow - CntLatestDt + 1) & ":F" & LastRow
    End With
End Sub