Display last row of Sheet in listbox on userform

newbieX · Jun 13, 2016

I have a userform with a sheet (Sheet1) filled with about 20 columns of data. I want to create a button (cmd1) that when clicked will populate a list box (Listbox1) with the data from just the last row of Sheet1. (Which would be the last data added to this sheet.) ColumnA is required and will never be blank except for the row after last row.

I have been monkeying with Add.Item but nothing displays correctly (e.g. data is displayed vertically as a single column instead of as several columns in a single row.) Plus I suspect I am making this way too complicated by adding all 20+ columns.

How can I accomplish this?

skywriter · Jun 13, 2016

Try this.

Code:

Private Sub CommandButton1_Click()
Dim lr As Long
    With Worksheets("Sheet1")
        lr = .Cells(.Rows.Count, 1).End(xlUp).Row
        Me.ListBox1.ColumnCount = 20
        Me.ListBox1.RowSource = .Range(.Cells(lr, 1), .Cells(lr, 20)).Address
    End With
End Sub

skywriter · Jun 13, 2016

This code would be better, in case a different sheet is the active sheet.

Code:

Private Sub CommandButton1_Click()
Dim lr As Long
    With Worksheets("Sheet1")
        lr = .Cells(.Rows.Count, 1).End(xlUp).Row
        Me.ListBox1.ColumnCount = 20
        Me.ListBox1.RowSource = .Name & "!" & .Range(.Cells(lr, 1), .Cells(lr, 20)).Address
    End With
End Sub

newbieX · Jun 14, 2016

The code works great so I will mark as solved but I was wondering if I could put headers (1st row) in the listbox. I know setting the ColumnsHead = True will display the row immediately above (which is not what I want) but I was thinking that maybe if I filter the data instead? For example, the value in TextBox5 is distinct. It will never appear twice in the spreadsheet, so if I can just grab the row that has whatever is entered into TextBox9 from ColumnI (Header = "ID") This column is where the TextBox9 value is stored when the "Save" button is clicked (Cmd1). Would this work? I am not sure how to code filtering and leave header.

Should I start new thread on this?

skywriter · Jun 14, 2016

This assumes row 1 is where the headers are.

Code:

Private Sub CommandButton1_Click()
Dim lr As Long
Dim x(1 To 2, 1 To 20), y As Long
    With Worksheets("Sheet1")
         lr = .Cells(.Rows.Count, 1).End(xlUp).Row
        Me.ListBox1.ColumnCount = 20
        For y = 1 To 20
            x(1, y) = .Cells(1, y)
            x(2, y) = .Cells(lr, y)
        Next y
              Me.ListBox1.List = x
    End With
End Sub

newbieX · Jun 14, 2016

Worked like a charm. I knew I was making it harder than it needed to be. You are AWESOME! Thank you.

skywriter · Jun 15, 2016

My pleasure, thanks for the feedback.

Display last row of Sheet in listbox on userform

newbieX

New Member

Excel Facts

skywriter

Well-known Member

skywriter

Well-known Member

newbieX

New Member

skywriter

Well-known Member

newbieX

New Member

skywriter

Well-known Member

Similar threads

Forum statistics

Share this page

Display last row of Sheet in listbox on userform

newbieX

New Member

Excel Facts

skywriter

Well-known Member

skywriter

Well-known Member

newbieX

New Member

skywriter

Well-known Member

newbieX

New Member

skywriter

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock