Excel VBA ListView control columns auto-fit (auto-resize)

Briefly, the subroutine code at the end of the page can be used to auto-resize (auto-fit) columns for Microsoft List View control used in user-forms in Excel, PowerPoint, Word, and the rest of MS Office tools. It is a simple, easy to understand, and short method to do this job. It can work with different font types and sizes.

The following pictures shows an actual example for list view control before and after the auto-resize subroutine (data source of table contents from:  http://www.7continents5oceans.com/ )



Column width is previously set to 100

Column width is auto-fit

' A procedure to auto-resize column width of list view control Sub AutoResizeListView(MyListView As Variant)   ' Create a dynamic label and set it invisible Set MyLabel = Me.Controls.Add("Forms.Label.1", "Test Label", True)   With MyLabel     .Font.Size = MyListView.Font.Size     .Font.Name= MyListView.Font.Name     .WordWrap = False     .AutoSize = True     .Visible = False End With   ' Auto-resize the first column MyLabel.Caption = MyListView.ColumnHeaders(1).Text MaxColumnWidth = MyLabel.Width   For i = 1 To MyListView.ListItems.Count MyLabel.Caption = MyListView.ListItems(i).Text If MyLabel.Width > MaxColumnWidth Then MaxColumnWidth = MyLabel.Width End If Next i MyListView.ColumnHeaders(1).Width = MaxColumnWidth + 8   ' Auto-resize the rest of columns For i = 1 To MyListView.ColumnHeaders.Count - 1 MyLabel.Caption = MyListView.ColumnHeaders(i + 1).Text MaxColumnWidth = MyLabel.Width For j = 1 To MyListView.ListItems.Count MyLabel.Caption = MyListView.ListItems(j).SubItems(i) If MyLabel.Width > MaxColumnWidth Then MaxColumnWidth = MyLabel.Width End If Next j     'Next cell row in the current column   MyListView.ColumnHeaders(i + 1).Width = MaxColumnWidth + 8    Next i     'Next column   Me.Controls.Remove MyLabel.Name    'Remove the dynamic label End Sub

This sub can be called using the following method inside the user-form code

Call AutoResizeListView(ListViewName)