Welcome toExcel.Tips.Net
Tips.Net Home
ExcelTips Home
Ask an Excel Question
Make a Comment
ExcelTips FAQ
ExcelTips Premium
Learn Access Now
Free Printable Forms
Beauty Tips
Car Tips
Cleaning Tips
College Tips
Cooking Tips
Excel2007 Tips
ExcelTips
Family Tips
Gardening Tips
Health Tips
Home Tips
Legal Tips
Money Tips
Organizing Tips
Pest Tips
Pet Tips
Wedding Tips
Word2007 Tips
WordTips
Advertise on the
ExcelTips Site
Adding a Little Animation to Your Life
Converting a Range of URLs to Hyperlinks
Making the Formula Bar Persistent
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 (3827) applies to Microsoft Excel versions: 97 2000 2002 2003 2007
Save Time! ExcelTips has been published weekly since late 1998. Past issues of ExcelTips are available in convenient ExcelTips archives. Have your own enhanced archive of ExcelTips at your fingertips, available to use at any time!