新增額外行的 Excel VBA 程式碼

Question

我正在 Excel 中建立 VBA 程式碼來新增行並設定其格式。

我需要「i」的值是一個變數（而不是如圖所示的 20），取決於 Excel 工作表第一列中的條目數。

Sub NextLine()

'
' AddLine Macro
' Adds Line

Dim i As Integer
i = 20

    ActiveCell.Offset(1, 0).Select '1 row down
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.SmallScroll ToRight:=1
    Range("$A$1:$M$" & i).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlHairline
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlHairline
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlHairline
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlHairline
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlHairline
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlHairline
    End With
    ActiveSheet.PageSetup.PrintArea = "$A$1:$M$" & i

End Sub

Answer 1

更換線路：

i=20

和：

With ActiveSheet
    i = .Cells(.Rows.Count, "A").End(xlUp).Row 
End With

它計算 A 列上包含資料的行數。

順便說一句，我測試了你的宏，它沒有添加一行。