VBA & Macro Problems

How to Remove the Extra Blank Column in an Excel VBA ListView

Steve K Oct 13, 2026 871 views

Question details

The user needs to remove an apparent extra blank column that appears on the right side of an Excel VBA ListView control after populating it with an ADO recordset.

Remove the Extra Blank Column from an Excel VBA ListView

Product: Microsoft Excel / WPS Spreadsheet
Device & OS: not provided
Scenario: Populating a VBA ListView control dynamically using fields from an ADO recordset.
Observed behavior: An empty space resembling an extra blank column appears on the right edge of the ListView because the control's total width exceeds the combined widths of the populated columns.

Before you start

Before modifying your VBA code, verify that your ADO recordset is returning the correct number of fields and that your ListView_Header procedure is not accidentally adding an empty header.

Solution 1Recommended

Adjust ListView or Column Widths programmatically

Fix the issue by ensuring the ListView control width perfectly matches the total width of all generated columns, or by extending the last column to fill the gap.

The perceived 'extra column' is usually just empty, unused space within the ListView control. This happens when the overall width of the control is larger than the sum of the individual column header widths.

To resolve this, you can either reduce the width of the ListView control itself or increase the width of your columns (typically the last column) to fill the remaining space.

Open the VBA Editor

Press Alt + F11 to open the VBA Editor and locate the UserForm containing your ListView control.

Verify header creation

Check your ListView_Header procedure to confirm it clears existing headers and strictly adds only one column for each ADO recordset field.

Calculate total column width

In your population loop, add up the Width property of all generated ColumnHeaders to find the total used space.

Resize the control or the last column

After populating the headers, add a line of code to either set 'ListView1.Width' to your calculated total width, or set 'ListView1.ColumnHeaders(ListView1.ColumnHeaders.Count).Width' to fill the remaining control width.

Adjust ListView or Column Widths programmatically

Handling Auto-Resized Columns: If your code automatically resizes columns based on the loaded recordset data, ensure that your control or column width adjustment runs after the auto-sizing routine completes.

WPS VBA Editor

Manage VBA ListViews Easily in WPS Spreadsheet

WPS Spreadsheet fully supports VBA macros and UserForms, including ActiveX ListView controls. You can effortlessly adjust control properties to fix UI layout issues like extra blank space directly within the WPS Macro Editor.

1. Access the Developer Tab: Open WPS Spreadsheet and navigate to the 'Developer' tab on the main ribbon.
2. Launch the Macro Editor: Click on 'Visual Basic' to open the WPS Macro Editor environment.
3. Open your UserForm: Double-click your specific UserForm in the Project Explorer panel to view the UI design.
4. Adjust properties manually: Select the ListView control and modify its 'Width' in the Properties window to closely match your expected column widths.

Fully compatible with Microsoft Excel VBA syntaxSupports ActiveX controls and UserForm editingLightweight macro execution environmentHigh compatibility with Microsoft Excel formats (.xlsm, .xlsb)

microsoft office alternative - wps office

Frequently Asked Questions

Why does my VBA ListView have empty space on the right?

The empty space appears when the total width of your defined columns is less than the overall width of the ListView control itself. It is not an actual data column but rather unallocated control background space.

Can I auto-size the last column to fill the empty ListView space?

Yes. You can programmatically calculate the remaining width by subtracting the total width of all other columns from the ListView's interior width, and then assign this calculated value to the last column's Width property.

Does clearing the ListView data remove the empty space?

No. Using 'ListView.ListItems.Clear' or 'ListView.ColumnHeaders.Clear' only removes the populated data and headers. The overall physical dimensions of the control remain unchanged until you explicitly adjust the Width property.

Excel & Data Problems