Written by Allen Wyatt (last updated December 21, 2024)
This tip applies to Excel 97, 2000, 2002, and 2003
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 (3827) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) 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!
Add a graphic to a worksheet as part of an Image object, and you can use some very handy event handlers to figure out the ...
Discover MoreNeed to put together a bunch of characters to create a text string? You can do it in your macros by using the String ...
Discover MoreEven if you do nothing but record macros, sooner or later you will have a need to edit what you record. Here's how to get ...
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 menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon 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