vba 根据列的内容在 Excel 中自动调整列大小
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27141944/
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
AutoFit column size in Excel based on content of column
提问by cRIMSOn ffs
I would like to use a VBA code which I can run for all worksheets in a workbook to resize the columns in the following sequence:
我想使用一个 VBA 代码，我可以为工作簿中的所有工作表运行该代码，以按以下顺序调整列的大小：
1.) First, resize all columns in all worksheets to width 10
1.) 首先，将所有工作表中的所有列调整为宽度 10
2.) Secondly, identify any column which have more TEXT than NUMBERS and Autosize the column to fit the contents.
2.) 其次，识别任何具有比 NUMBERS 多的 TEXT 并自动调整列以适应内容的列。
The purpose of the changes is to provide a standard column width layout for our financial reports which get populated in Excel via Hyperion Smart View. All cells come out formatted as 'Custom' in Excel and cannot be changed. The report data (actual numbers) could start on any row within the spreadhseet, as the column titles sometimes take between 3 to 10 rows (maybe more). We have a lot of butterfly reports (dimensions in the middle 'say column c', with facts (numbers) to the left and right of that column). In some cases a column titled 'Actuals' in column A might have a sub-heading 'Revenue' further down (Row 50) in the same column. Therefore it's fair to say that there is a mix of numbers and text in columns with no set rule.
更改的目的是为我们的财务报告提供标准的列宽布局，这些报告通过 Hyperion Smart View 填充到 Excel 中。所有单元格在 Excel 中都以“自定义”格式出现并且无法更改。报告数据（实际数字）可以从电子表格中的任何行开始，因为列标题有时需要 3 到 10 行（可能更多）。我们有很多蝴蝶报告（中间“说 c 列”的维度，该列的左侧和右侧有事实（数字））。在某些情况下，A 列中标题为“实际数据”的列可能在同一列的更下方（第 50 行）有一个子标题“收入”。因此，可以说在没有固定规则的列中混合了数字和文本。
Please help with the above or suggest any alternatives to achieve a code which can ensure that the number columns are all the same width (10) and the text column is 'Autofit' based on the size.
请帮助解决上述问题或建议任何替代方法来实现代码，该代码可以确保数字列的宽度都相同（10），并且文本列是基于大小的“自动调整”。
So far I have the below, which updates all column width to size 10 and then manually identifies the columns from worksheet BM03 and BM10 for AutoFit:
到目前为止，我有以下内容，它将所有列宽更新为大小 10，然后手动识别工作表 BM03 和 BM10 中的列以进行 AutoFit：
Sub Run_Me_To_Fix_Columns()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
Call resizingColumns(ws)
If ws.name = "BM03a EBIT Report (Summary & De" Then
ws.Columns("I:I").EntireColumn.Autofit
ElseIf ws.name = "BM10 Balance Sheet" Then
ws.Columns("A:A").EntireColumn.Autofit
End If
Next
End Sub
Sub resizingColumns(ws As Worksheet)
With ws
.Range("A:X").ColumnWidth = 10
End With
End Sub
采纳答案by teylyn
You could try to evaluate each column in each worksheet and compare the number of text cells with the number of numeric cells. Then format to autofit those that have more text than numbers.
您可以尝试评估每个工作表中的每一列，并将文本单元格的数量与数字单元格的数量进行比较。然后格式化以自动适应那些文本多于数字的内容。
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
Call resizingColumns(ws)
' If ws.Name = "BM03a EBIT Report (Summary & De" Then
' ws.Columns("I:I").EntireColumn.AutoFit
'
' ElseIf ws.Name = "BM10 Balance Sheet" Then
' ws.Columns("A:A").EntireColumn.AutoFit
'
' End If
Next
End Sub
Sub resizingColumns(ws As Worksheet)
With ws
.Range("A:X").ColumnWidth = 10
End With
For i = 1 To 24
Numbers = WorksheetFunction.Count(ws.Columns(i))
Text = WorksheetFunction.CountA(ws.Columns(i)) - Numbers
If Numbers < Text Then
ws.Columns(i).EntireColumn.AutoFit
End If
Next i
ws.Range("A1").Select
End Sub
