霍华德需要发现工作表中所有给定宽度的列。例如,他需要知道哪些列的宽度为3.6。

这可以通过使用宏来完成。宏可以访问的属性之一是每一列的宽度。这意味着您可以按以下方式逐步浏览各列,并对照所需的宽度(3.6)检查这些宽度:

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

此宏显示一个消息框,其中列出了与所需宽度匹配的列。通过一些简单的更改就可以使宏更加健壮。例如,以下示例提示用户输入列宽,计算匹配数,甚至补偿工作表是否使用R1C1引用模式。

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_网站上的任何其他页面)中描述的宏,我准备了一个特殊页面,其中包含有用的信息。

_ExcelTips_是您进行经济高效的Microsoft Excel培训的来源。

本技巧(3827)适用于Microsoft Excel 97、2000、2002和2003。可以在以下功能区中为Excel的功能区界面找到该技巧的版本(Excel 2007及更高版本):