Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, and 2016. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Finding Columns of a Certain Width.
by Allen Wyatt
(last updated December 26, 2015)
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
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (11654) applies to Microsoft Excel 2007, 2010, 2013, and 2016. You can find a version of this tip for the older menu interface of Excel here: Finding Columns of a Certain Width.
Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!
Ever wonder what the macro-oriented equivalent of pressing Ctrl+End is? Here's the code and some caveats on using it.Discover More
Need to know how many characters there are in a workbook? You can find out easily with the handy macro introduced in this ...Discover More
When you use a macro to do file operations, it works (by default) within the current directory. If you want to know which ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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.