使用 VBA 自动调整行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9292476/
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 rows using VBA
提问by user569125
I am trying to do AutoFit cells in the range A2 to AA2.
我正在尝试在 A2 到 AA2 范围内执行 AutoFit 单元格。
Below is the code I am trying:
下面是我正在尝试的代码：
Application.ScreenUpdating = False
Dim wkSt As String
Dim wkBk As Worksheet
wkSt = ActiveSheet.Name
For Each wkBk In ActiveWorkbook.Worksheets
On Error Resume Next
wkBk.Activate
Cells.Range("A2:AA2").EntireColumn.AutoFit
Next wkBk
Sheets(wkSt).Select
Application.ScreenUpdating = True
Below is some sample Excel data, as displayed on the screen:
下面是一些示例 Excel 数据，如屏幕上所示：
A B C
1 Jhon is the best employee
2 Emp_Name Emp_id Empsal.
Note that "Jhon is the best employee" is all in one cell (A1) but when displayed its contents overlap cells B1 and C1.
请注意，“Jhon 是最好的员工”全部在一个单元格 (A1) 中，但在显示时其内容与单元格 B1 和 C1 重叠。
With the above code, the data is then displayed as follows:
有了上面的代码，然后数据显示如下：
A B C
1 Jhon is the best employee
2 Emp_Name Emp_id Empsal
How can I do AutoFit on each cell of the second row in each worksheet? Above code failing to autofit data from my second row.
如何对每个工作表中第二行的每个单元格进行自动调整？上面的代码无法从我的第二行自动调整数据。
This is what I want the display to look like:
这就是我希望显示的样子：
A B C
1 Jhon is the best employee
2 Emp_Name Emp_id Empsal.
i.e. the columns should fit snuggly around "Emp_Name", "Emp_id" and "Empsal." -- not around "Jhon is the best employee".
即，列应该紧贴“Emp_Name”、“Emp_id”和“Empsal”。-- 不是围绕“Jhon 是最好的员工”。
EDITAlso, if there are data in the following rows (3, 4, etc.) then these should be included in the Autofit as well. For example, data should display like below:
编辑此外，如果以下行（3、4 等）中有数据，则这些也应包含在 Autofit 中。例如，数据应显示如下：
A B C
1 Jhon is the best employee
2 Emp_Name Emp_id Empsal.
3 Jhon Peter 2 2000
回答by assylias
If what you want is to force Autofit to ignore the first row and only Autofit based on the content of rows 2 and greater. I don't think this is possible. One way to do it would be to store the content of the first row, delete it, autofit and insert the content of the first row back:
如果您想要的是强制自动调整忽略第一行，并且仅根据第 2 行及更大行的内容自动调整。我不认为这是可能的。一种方法是存储第一行的内容，删除它，自动调整并插入第一行的内容：
Public Sub test()
Dim wkSt As String
Dim wkBk As Worksheet
Dim temp As Variant
wkSt = ActiveSheet.Name
For Each wkBk In ActiveWorkbook.Worksheets
On Error Resume Next
wkBk.Activate
temp = wkBk.Rows(1)
wkBk.Rows(1).ClearContents
wkBk.Columns("A:AA").AutoFit
wkBk.Rows(1) = temp
Next wkBk
Sheets(wkSt).Select
End Sub
回答by Jean-Fran?ois Corbett
Instead of doing Autofit on EntireColumn, do it on Columns.
不要在 上进行 Autofit EntireColumn，而是在 上进行Columns。
wkBk.Range("A2:AA2").Columns.AutoFit
Have a look at VBA help for AutoFit...
看看 AutoFit 的 VBA 帮助...
EDITYou now want to do AutoFit not only for row 2, but also 3, 4, 5 etc. This requires only a simple modification:
编辑您现在不仅要对第 2 行进行 AutoFit，还要对第 3、4、5 行等进行自动调整。这仅需要进行简单的修改：
wkBk.Range("A2:AA999").Columns.AutoFit
The above assumes that row 999 is your last row with data to be autofitted. You can also include all rows all the way down to the bottom of the sheet like this:
以上假设第 999 行是您要自动调整数据的最后一行。您还可以像这样将所有行一直包含到工作表的底部：
wkBk.Range(Range("A2"), Cells(wkBk.Rows.Count, "AA")).Columns.AutoFit
This should be enough to get you on the right track.
这应该足以让您走上正轨。
