时间戳

首页 > 解决方案 > 无法插入超出范围的行？

excel - 无法插入超出范围的行？

问题描述

我编写了以下代码，以便将 rngtocopy ABOVE rngins....

现在我尝试了很多，它一直在 rngins 下面添加它，我不知道为什么。我尝试了 xlshiftup，它实际上给了我错误，可能是因为上面有值？

Sub reviewverschieben()

Dim counter As Long, lrow As Long, lrowrev As Long, i As Long, lastrev As Long
Dim ws As Worksheet
Dim rngtocopy As Range, rngins As Range
Dim lastcolumn As String

Set ws = ActiveSheet
Rows.EntireRow.Hidden = False

counter = 0

With ws
lrow = .Cells(Rows.Count, 1).End(xlUp).row
Do While counter = 0
  For i = 32 To lrow
    If .Cells(i, 1).Value = "Review Participants" And counter = 1 Then
     lrowrev = i

    ElseIf .Cells(i, 1).Value = "Review Participants" And i <> lrow Then
     counter = counter + 1
     lastrev = i  'row nr which we take as a reference to insert new table above
     lrowrev = lastrev
     lcol = .Cells(i + 1, .Columns.Count).End(xlToLeft).Column 'last meeting of the review is our reference for lastcol

        ElseIf counter = 1 And i = lrow Then
        lrowrev = lrow + 2
        Exit For
End If
Next
Loop

lastcolumn = Split(Cells(, lcol).Address, "$")(1)
Set rngtocopy = .Range("A" & 32 & ":" & lastcolumn & lrowrev)

Debug.Print rngtocopy.Address

Set rngins = .Range("A" & 32 & ":" & lastcolumn & lrowrev)
Debug.Print rngins.Address

        'Range("A" & lrow).Offset(5).EntireRow.Hidden = False
             rngtocopy.Copy
             rngins.Insert Shift:=xlShiftDown
             ringins.PasteSpecial Paste:=xlPasteAll

图像以获得更好的说明，我现在拥有的

标签： excelvba

解决方案

考虑到这段小代码杂耍，您的 RngIns 规范很可能被描述为冒险：lastcolumn = Split(Cells(, lcol).Address, "$")(1). 我建议您像这样定义范围。

Set rngIns = .Range(.Cells(32, "A"), .Cells(lrowrev, lcol))

该代码定义了范围的第一个和最后一个单元格，这使您可以轻松地遵循。现在，如果您在rngIns 插入，则插入将低于该范围。如果您在RngIns.Offset(1) 处插入，则插入将在 rngIns 上方进行。当然，您可以通过不同地定义 rngIns 的行来产生同样的差异，也许像Set rngIns = .Range(.Cells(33, "A"), .Cells(lrowrev + 1, lcol)).

但是，我想知道你为什么要插入单元格。插入这么多工作表行然后粘贴到空白行不是更容易吗？

推荐阅读