☰

Excel VBA 代码添加额外行

tag-icon • tag-icon microsoft-excel vba
Excel VBA 代码添加额外行

我正在 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

答案1

替换以下行：

i=20

和：

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

它计算 A 列中包含数据的行数。

顺便说一句，我测试了你的宏，它没有添加行。

相关内容