vba Excel中的VBA - 如何在每个表格前自动添加一个空白列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17418157/
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
VBA in Excel - How to automatically add a blank column before each table
提问by Vijnanamatrata
I have a task which needs automation with use of macro and I have written code to do it; however, my code could not do the task successfully. Therefore, I need your help now.
我有一个需要使用宏实现自动化的任务，我已经编写了代码来完成它；但是，我的代码无法成功完成任务。因此，我现在需要你的帮助。
Please download my sample file, including 3 sheets: “start_1”, “start_2” and “result”. “start_1” and “start_2” are identical. I need to format tables in these two sheets “start” so that they look like exactly the “result” sheet.
请下载我的示例文件，包括 3 张表：“start_1”、“start_2”和“result”。“start_1”和“start_2”是相同的。我需要在这两个工作表“开始”中格式化表格，使它们看起来完全像“结果”工作表。
http://www.mediafire.com/download/n8dwli8v55lw9gl/add_a_blank_column_before_each_table.xls
http://www.mediafire.com/download/n8dwli8v55lw9gl/add_a_blank_column_before_each_table.xls
In detail, here are tasks which need automating:
详细来说，以下是需要自动化的任务：
- Add 1 blank row at top (I have done it)
- Freeze title rows (I have done it)
- This is what I could not do: Add 1 blank column before each table. The added column must have no fill color and no border at all. (Each table here is identified by merged cells in the top title row)
- Also please show me how to determine the last column with data and the last row with data so that the blank outer space is hidden.
- 在顶部添加 1 个空白行（我已经完成了）
- 冻结标题行（我已经做到了）
- 这是我不能做的：在每个表之前添加 1 个空白列。添加的列必须没有填充颜色和边框。（此处的每个表格均由顶部标题行中的合并单元格标识）
- 还请告诉我如何确定带有数据的最后一列和带有数据的最后一行，以便隐藏空白的外部空间。
Thank you very much for your help.
非常感谢您的帮助。
Below is my code (in ThisWorkbook module):
下面是我的代码（在 ThisWorkbook 模块中）：
Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim bScrUpdate As Boolean
Dim ws As Worksheet
Dim rng As Range
Application.EnableCancelKey = xlDisabled 'disable ESC key
bScrUpdate = Application.ScreenUpdating
If bScrUpdate = True Then Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "result" Then
ws.Select
Rows("1:1").Insert Shift:=xlDown
Range("A4").Select
ActiveWindow.FreezePanes = True
For Each rng In Rows("2:2").Cells
If rng.MergeCells Then
rng.MergeArea.Cells(1, 1).Select
Selection.Offset(-1, 1).EntireColumn.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
End If
Next rng
End If
Next ws
Application.DisplayAlerts = True
If Not Application.ScreenUpdating = bScrUpdate _
Then Application.ScreenUpdating = bScrUpdate
Application.EnableCancelKey = xlInterrupt 'enable ESC key
End Sub
回答by MiVoth
To add a column and clear all of its formatting, you can do something like this:
要添加一列并清除其所有格式，您可以执行以下操作：
Sub InsertCol()
Columns(1).Insert
Columns(1).ClearFormats ' might be unnecessary
End Sub
If you want to determine the last row/column look at this question.
如果您想确定最后一行/列，请查看此问题。
