VBA & Macro Problems

How to Select the Last Populated Row in an Excel VBA ListBox

John Wilson Oct 22, 2026 868 views

Question details

The user needs to use Excel VBA to search a multicolumn ListBox from the bottom upwards and automatically select the last row that contains data in its first column.

How to Select the Last Populated Row in an Excel VBA ListBox

Product: Excel
Device & OS: not provided
Scenario: Developing or troubleshooting a UserForm in Excel VBA that involves dynamically finding and selecting data within a multicolumn ListBox.
Observed behavior: The goal state is to successfully iterate through the ListBox items in reverse order and highlight the last non-empty entry without causing an out-of-bounds error.

Before you start

Ensure your Excel workbook contains a UserForm with a multicolumn ListBox (e.g., named 'listdatabase') and a trigger element, such as a Command Button, before adding the macro.

Solution 1Recommended

Use a Backward Loop to Find and Select the Last Populated Row

Iterate backwards through the ListBox items using a Step -1 loop to find the first non-blank entry from the bottom in the target column.

In Excel VBA, ListBox items are zero-indexed, meaning the last item is always positioned at 'ListCount - 1'. By starting a loop at the very bottom and stepping backwards, the code will naturally encounter the last populated row first. Once found, the script assigns the index to highlight it and exits the loop to save processing time.

Open the VBA Editor

Press ALT + F11 in Excel to open the Visual Basic for Applications (VBA) Editor. Locate your project in the Project Explorer and double-click the UserForm containing your ListBox.

Access the Command Button Event

Double-click the Command Button on your UserForm (e.g., 'Cmdlist2') to open its Click event code window.

Insert the Backward Loop Code

Copy and paste the following code into the event block: Option Explicit Private Sub Cmdlist2_Click() Dim i As Long With Me.listdatabase For i = .ListCount - 1 To 0 Step -1 If .List(i, 0) <> "" Then .ListIndex = i Exit For End If Next i End With End Sub

Run and Test the Macro

Press F5 to run the UserForm. Click your Command Button. The loop will start at the last ListBox item and select the first nonblank value it finds in column zero (the first column).

Use a Backward Loop to Find and Select the Last Populated Row

Adjusting the Column Index: In the code '.List(i, 0)', the '0' represents the first column. If you need to check the second column for data instead, change this value to '1'.

Advanced Macro Support

Write and Run VBA Macros Easily with WPS Office

WPS Spreadsheet fully supports Visual Basic for Applications (VBA). You can build UserForms, manipulate ListBoxes, and execute your Excel macros seamlessly within WPS Office's highly compatible environment.

1. Open your macro workbook in WPS Office: Launch WPS Spreadsheet and open the .xlsm or .xlsb file that contains your ListBox UserForm.
2. Enable macros: If a security warning appears at the top of the workspace, click 'Enable Macros' to allow your VBA code to run.
3. Open the WPS Macro Editor: Navigate to the Developer tab and click on 'Visual Basic', or simply press ALT + F11 on your keyboard.
4. Run your UserForm: Locate your UserForm in the Project panel, press F5 to run it, and click your button to instantly select the last populated row in your ListBox.

Full compatibility with Microsoft Excel VBA syntax and UserForm propertiesLightweight software architecture ensuring macros execute with high performanceSeamlessly open, edit, and save .xlsm and .xlsb macro-enabled formatsFamiliar user interface making migration from Excel effortless

microsoft office alternative - wps office

Frequently Asked Questions

Why does the VBA loop start at ListCount - 1?

In Excel VBA, ListBox indexes are zero-based. This means a ListBox with 10 items is indexed from 0 to 9. Starting your loop at 'ListCount - 1' ensures you check the very last item first without triggering a 'Subscript out of range' error.

Can I check a different ListBox column for data instead of the first one?

Yes. In the condition '.List(i, 0) <> ""', the '0' represents the first column. Simply change this number to '1' for the second column, '2' for the third column, and so on, depending on your multicolumn layout.

How do I select multiple rows in an Excel VBA ListBox?

To select multiple rows, you must first change the ListBox's MultiSelect property to 'fmMultiSelectMulti' or 'fmMultiSelectExtended'. Then, instead of setting the '.ListIndex', use the '.Selected(i) = True' property inside your VBA loop for the rows you want to highlight.

Why is my VBA ListBox not showing all columns?

If your multicolumn ListBox is only showing the first column, ensure that the 'ColumnCount' property is set to the correct number of columns (e.g., 3). You may also need to adjust the 'ColumnWidths' property to ensure each column has enough space to be visible.

Excel & Data Problems