vba 如何在 Excel 中动态插入列？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16930000/
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 do I insert columns dynamically in Excel?
提问by xyz
I would like to insert separating columns into an Excel report to make the existing columns easier to view.
我想将分隔列插入 Excel 报告中，以使现有列更易于查看。
The report is created dynamically and I never know how many columns there will be; there could be 5, 10, 17, etc.
报告是动态创建的，我永远不知道会有多少列；可能有 5、10、17 等。
The section starts at F and goes to ival=Application.WorksheetFunction.CountIf(range("D2:D" & LastRow), "Other")
该部分从 F 开始并转到 ival=Application.WorksheetFunction.CountIf(range("D2:D" & LastRow), "Other")
So if ival=10then the columns are F G H I J K L M N O, and I need to insert columns between F&G, G&H, H&I, I&J, ... and N&O.
因此，如果ival=10这些列是 FGHIJKLMNO，我需要在 F&G、G&H、H&I、I&J 和 N&O 之间插入列。
This may be a possibility for inserting columns: Workbooks("yourworkbook").Worksheets("theworksheet").Columns(i).Insert
这可能是插入列的可能性： Workbooks("yourworkbook").Worksheets("theworksheet").Columns(i).Insert
But I'm not sure how to loop through ival.
但我不确定如何遍历ival.
Sub InsertColumns()
Dim iVal As Integer
Dim Rng As range
Dim LastRow As Long
Dim i As Integer
With Sheets("sheet1")
LastRow = .range("D" & .Rows.Count).End(xlUp).Row
End With
iVal = Application.WorksheetFunction.CountIf(range("D2:D" & LastRow), "Other")
For i = 7 To iVal - 1
Workbooks("yourworkbook").Worksheets("theworksheet").Columns(i+1).Insert
Next i
End Sub
回答by Santosh
The below code should work without needing to worry about ival:
下面的代码应该可以工作而无需担心ival：
Sub InsertSeparatorColumns()
Dim lastCol As Long
With Sheets("sheet1")
lastCol = Cells(2, .Columns.Count).End(xlToLeft).Column
For i = lastCol To 7 Step -1
.Columns(i).Insert
.Columns(i).ColumnWidth = 0.5
Next
End With
End Sub
回答by Jon Crowell
Try this:
尝试这个：
Sub InsertSeparatorColumns()
Dim ws as Worksheet
Dim firstCol As String
Dim lastRow As Long
Dim i As Long
Dim howManySeparators As Long
Set ws = ThisWorkbook.Sheets("Sheet1")
firstCol = "F"
lastRow = ws.Range("D" & ws.Rows.Count).End(xlUp).Row
howManySeparators = Application.WorksheetFunction.CountIf _
(ws.range("D2:D" & LastRow), "Other")
For i = 1 To howManySeparators * 2 Step 2
ws.Range(firstCol & 1).Offset(, i).EntireColumn.Insert
Next i
End Sub
