vba 如何遍历Excel中的每一列并根据条件应用列宽
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17712988/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to loop through each column in Excel and apply column width based on condition
提问by jjrc
I am in need of excel vba code that will autofit all columns by default and then loop through each column width and if any width exceeds a particular value eg., 50, then restrict that particular column width to 30 and sets word wrap to true.
我需要 excel vba 代码，默认情况下会自动调整所有列，然后循环遍历每个列的宽度，如果任何宽度超过特定值，例如 50，则将该特定列的宽度限制为 30，并将自动换行设置为 true。
Public Function LastColumn(Optional wks As Worksheet) As Long
If wks Is Nothing Then: Set wks = ActiveSheet
LastColumn = Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
End Function
Sub Macro1()
Dim LastCol As Long
Cells.Select
Cells.EntireColumn.AutoFit
LastCol = LastColumn(ThisWorkbook.Sheets("Sheet1"))
For i = 1 To LastCol
If Columns(i).ColumnWidth > 70 Then
Columns(i).ColumnWidth = 70
Columns(i).WrapText = True
End If
Next i
End Sub
Is there a better way to achieve this?
有没有更好的方法来实现这一目标？
采纳答案by David Colwell
Something like
就像是
Sub Autofit()
Dim col As Range
For Each col In ActiveSheet.UsedRange.Columns 'Only columns that actually have values
col.Autofit
If col.ColumnWidth > 50 Then 'Set your values here
col.ColumnWidth = 30
col.WrapText = True
End If
Next
End Sub
Note, this uses Excel widths, not Pixels
请注意，这使用 Excel 宽度，而不是像素
