vba Excel 宏以使用预定义的格式和公式创建新行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19207422/
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
Excel Macro to make new rows with predefined formatting and formula
提问by user2851398
I have an excel sheet with more than 2000 rows.
我有一个超过 2000 行的 Excel 表格。
I use the below macro to add a blank rows with a change in value of cloumn A1.
我使用下面的宏添加一个空白行，并更改了 cloumn A1 的值。
Sub AddBlankRows()
'
Dim iRow As Integer
Range("a1").Select
'
iRow = 1
'
Do
'![enter image description here][1]
If Cells(iRow + 1, 1) <> Cells(iRow, 1) Then
Cells(iRow + 1, 1).EntireRow.Insert shift:=xlDown
iRow = iRow + 2
Else
iRow = iRow + 1
End If
'
Loop While Not Cells(iRow, 1).Text = ""
'
End Sub
Is there a way insert the row (same with the above macro)with a fourmula and a predefined formatting?
有没有办法插入带有 Fourmula 和预定义格式的行（与上面的宏相同）？
采纳答案by Santosh
Below is the sample code.
下面是示例代码。
Sub AddBlankRows()
Dim lastRow As Long
Dim iRow As Long
Dim cursor As Long
cursor = 2
With ThisWorkbook.Sheets("sheet1")
lastRow = .Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To lastRow
If (LCase(Trim(.Cells(i, 1))) <> LCase(Trim(.Cells(i + 1, 1)))) Then
.Cells(i + 1, 1).EntireRow.Insert shift:=xlDown
.Cells(i + 1, 1).EntireRow.Interior.Color = vbYellow
lastRow = lastRow + 1
.Cells(i + 1, 2) = WorksheetFunction.Count(.Range(.Cells(cursor, 2), .Cells(i, 2)))
.Cells(i + 1, 2).NumberFormat = "0"
.Cells(i + 1, 3) = WorksheetFunction.Sum(.Range(.Cells(cursor, 3), .Cells(i, 3)))
.Cells(i + 1, 3).NumberFormat = "0.00"
i = i + 2
cursor = i
End If
Next
.Cells(lastRow + 1, 1).EntireRow.Insert shift:=xlDown
.Cells(lastRow + 1, 1).EntireRow.Interior.Color = vbYellow
.Cells(lastRow + 1, 2) = WorksheetFunction.Count(.Range(.Cells(cursor, 2), .Cells(lastRow, 2)))
.Cells(lastRow + 1, 2).NumberFormat = "0"
.Cells(lastRow + 1, 3) = WorksheetFunction.Sum(.Range(.Cells(cursor, 3), .Cells(lastRow, 3)))
.Cells(lastRow + 1, 3).NumberFormat = "0.00"
End With
End Sub
