Written by Allen Wyatt (last updated November 28, 2020)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and 2021
Howard has a need to discover all the columns in a worksheet that are a given width. For instance, he needs to know which columns have a width of 3.6.
This can be done by using a macro. One of the properties your macro can access is the width of each column. This means that you can step through the columns and check those widths against the desired width (3.6) in the following manner:
Sub ListColumns() Dim dColWidth As Double Dim sMsg As String Dim x As Integer dColWidth = 3.6 sMsg = "" For x = 1 To ActiveSheet.Columns.Count If Columns(x).ColumnWidth = dColWidth Then sMsg = sMsg & vbCrLf & x End If Next If sMsg = "" Then sMsg = "There are no columns with" & _ vbCrLf & "a width of " & dColWidth Else sMsg = "The following columns have" & _ vbCrLf & "a width of " & dColWidth & _ ":" & vbCrLf & sMsg End If MsgBox sMsg End Sub
This macro displays a message box that lists the columns that match the desired width. The macro can be made more robust with some simple changes. For instance, the following example prompts the user for a column width, counts the number of matches, and even compensates if the worksheet is using R1C1 referencing mode.
Sub Find_ColumnWidth() Dim Col As Integer ' Column (loop variable) Dim ColsFound As Integer ' Columns Found Count Dim Desired_Width As Double ' Column Width To Find Dim OutStr As String ' Output String Dim Title As String ' Msgbox Title Dim I As Integer Dim S As String ' Find out column width wanted S = InputBox("Enter ColumnWidth to find ?", _ " Find ColumnWidth on " & ActiveSheet.Name) Desired_Width = Val(S) If Desired_Width = 0 Then Exit Sub ' Initialize Columns Found Count and Output String ColsFound = 0 OutStr = "" For Col = 1 To ActiveSheet.Columns.Count If Columns(Col).ColumnWidth = Desired_Width Then ColsFound = ColsFound + 1 If Application.ReferenceStyle = 1 Then ' Using "A1" format S = Cells(1, Col).Address(ReferenceStyle:=xlA1) S = Mid(S, 2, Len(S) - 3) Else ' Using "R1C1" format S = Trim(Str(Col)) End If OutStr = OutStr & S & vbCrLf End If Next ' Construct MsgBox Title string Title = "Width=" & Desired_Width _ & " on " & ColsFound & " column" _ & Left("s", - (ColsFound > 1)) & " " If ColsFound = 0 Then OutStr = "No matches found" End If MsgBox OutStr, vbOKOnly, Title End Sub
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (11654) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and 2021. You can find a version of this tip for the older menu interface of Excel here: Finding Columns of a Certain Width.
Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!
As your macro is processing information, there will doubtless be times that it will need to compare information in ...
Discover MoreIf you run a macro you used in an older version of Excel on a newer system, it may seem like the macro runs slower. Here ...
Discover MoreDoes your macro need to get some input from a user? Here are the ways that Excel provides for that input to be solicited.
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Comments