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.
Best-Selling VBA Tutorial for Beginners Take your Excel knowledge to the next level. With a little background in VBA programming, you can go well beyond basic spreadsheets and functions. Use macros to reduce errors, save time, and integrate with other Microsoft applications. Fully updated for the latest version of Office 365. Check out Microsoft 365 Excel VBA Programming For Dummies today!
Functions are a common programming construct. They help you to create easy ways of processing information and returning a ...
Discover MoreNeed to run a DOS command from within one of your macros? The answer is the Shell command, described in this tip.
Discover MoreNeed to rename a file in a macro? It's easy to do using the Name command, as discussed in this tip.
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