英文:
Can this code be streamlined with less "with" "End With" statements>?
问题
以下是翻译好的部分：
我提出这个问题是因为似乎有一种比为每一列单独使用“With”和“EndWith”更好的方法。
在活动工作表上，对于整个A1:C1列，将其设置为自动调整列宽并增加两个空格，以及对于整个D1:G1列，将其设置为自动调整列宽并增加一个空格。
子 AutoFitColumns()
Worksheets("TestSheet1").Activate
With Range("A1").EntireColumn
.AutoFit
.ColumnWidth = .ColumnWidth + 2
End With
With Range("B1").EntireColumn
.AutoFit
.ColumnWidth = .ColumnWidth + 2
End With
With Range("C1").EntireColumn
.AutoFit
.ColumnWidth = .ColumnWidth + 2
End With
With Range("D1").EntireColumn
.AutoFit
.ColumnWidth = .ColumnWidth + 1
End With
With Range("E1").EntireColumn
.AutoFit
.ColumnWidth = .ColumnWidth + 1
End With
With Range("F1").EntireColumn
.AutoFit
.ColumnWidth = .ColumnWidth + 1
End With
With Range("G1").EntireColumn
.AutoFit
.ColumnWidth = .ColumnWidth + 1
End With
End Sub
英文:
I ask as it seems there is a better way than having a separate "With" "EndWith for each Column.
On the active sheet
For the entire Columns A1:C1 it is setting to autofit plus two blank spaces, and
for the entire Columns D1:G1 it is setting to autofit plus one blank space.
Sub AutoFitColumns()
Worksheets("TestSheet1").Activate
With Range("A1").EntireColumn
.AutoFit
.ColumnWidth = .ColumnWidth + 2
End With
With Range("B1").EntireColumn
.AutoFit
.ColumnWidth = .ColumnWidth + 2
End With
With Range("C1").EntireColumn
.AutoFit
.ColumnWidth = .ColumnWidth + 2
End With
With Range("D1").EntireColumn
.AutoFit
.ColumnWidth = .ColumnWidth + 1
End With
With Range("E1").EntireColumn
.AutoFit
.ColumnWidth = .ColumnWidth + 1
End With
With Range("F1").EntireColumn
.AutoFit
.ColumnWidth = .ColumnWidth + 1
End With
With Range("G1").EntireColumn
.AutoFit
.ColumnWidth = .ColumnWidth + 1
End With
End Sub
答案1
得分: 3
例如：
Sub AutoFitColumns()
Dim c As Range
For Each c In Worksheets("TestSheet1").Range("A1:G1").Cells
With c.EntireColumn
.AutoFit
.ColumnWidth = .ColumnWidth + IIf(c.Column <= 3, 2, 1)
End With
Next c
End Sub
英文:
For example:
Sub AutoFitColumns()
Dim c As Range
For Each c In Worksheets("TestSheet1").Range("A1:G1").Cells
With c.EntireColumn
.AutoFit
.ColumnWidth = .ColumnWidth + IIf(c.Column <= 3, 2, 1)
End With
Next c
End Sub
答案2
得分: 0
以下是代码部分的翻译：
Sub AutoFitColumns()
Dim AutoFitProperties()
AutoFitProperties = Array(VBA.Array("A1:C1", 2), VBA.Array("D1:G1", 1))
With ThisWorkbook.Sheets("TestSheet1")
Dim Afp, crg As Range
For Each Afp In AutoFitProperties
With .Range(Afp(0)).EntireColumn
.AutoFit
For Each crg In .Columns
crg.ColumnWidth = crg.ColumnWidth + Afp(1)
Next crg
End With
Next Afp
End With
End Sub
注意：以上翻译只包括代码部分，不包括注释或其他内容。
英文:
Autofit Columns
<!-- language: lang-vb -->
Sub AutoFitColumns()
Dim AutoFitProperties()
AutoFitProperties = Array(VBA.Array("A1:C1", 2), VBA.Array("D1:G1", 1))
With ThisWorkbook.Sheets("TestSheet1")
Dim Afp, crg As Range
For Each Afp In AutoFitProperties
With .Range(Afp(0)).EntireColumn
.AutoFit
For Each crg In .Columns
crg.ColumnWidth = crg.ColumnWidth + Afp(1)
Next crg
End With
Next Afp
End With
End Sub
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论