☰

修改 VBA 宏以在电子表格中的任何位置添加多个空行

tag-icon • tag-icon microsoft-excel vba
修改 VBA 宏以在电子表格中的任何位置添加多个空行

我希望能够在 Excel 电子表格的任意位置添加空行。我需要指定各种不同的空行以输入电子表格中的各个行。例如，从第 99 行开始插入 100 个空行。

我是宏新手，从互联网上复制了一个宏并尝试改编它，但我无法使起始行正确。我在代码注释中标注了代码中令我困惑的部分。

Sub test()
Dim j As Long, r As Range

h = 0

j = 0


h = InputBox("type starting row")

j = InputBox("type the number of rows to be inserted")

Set r = Range("A2")  'Problem here -- I need to be able to change this to value from h'

Range(r.Offset(h, 0), r.Offset(j, 0)).EntireRow.Insert

Set r = Cells(r.Row + j, 1)

'MsgBox r.Address(the apostrophe in the beginning of this line makes this line non operable)


End Sub

答案1

Sub Macro1()

'You should give your variables meaningfull names
'But I will leave them as h and j for now.
Dim h As Integer
Dim j As Integer

j = InputBox("How many rows do you want?")

h = InputBox("At what line do you want them?")

    'You can use the variable in the range, this is how
    Range("A" & h).Select

    'This is called a FOR loop, google them - they are easy to use and a basic part of programming.
    For i = 1 To j
    Selection.EntireRow.Insert
    Next i

End Sub

注意：这不是最优雅的解决方案，它主要是为了易于理解而编写的。

您还可以通过不首先选择范围然后在选择处插入来缩短它一点，如下所示：

For i = 1 To j
Range("A" & h).EntireRow.Insert
Next i

答案2

您只需对代码进行一些调整——它几乎可以正常工作。请参阅下面修改后的代码中的我的注释。

Sub test()
Dim j As Long, r As Range

h = 0

j = 0


h = InputBox("type starting row")

j = InputBox("type the number of rows to be inserted")

'I moved this up one row so that the inserted rows are just below row h.
Set r = Range("A1")

'The second argument indicates the address of the bottom of the range. 
'This needs to take h into account so that the difference between the top and bottom is j rows.
Range(r.Offset(h, 0), r.Offset(h + j - 1, 0)).EntireRow.Insert

'The rest of the code wasn't doing anything, so I removed it.

End Sub

相关内容