VBA:将下面的值移动到清空的单元格

Question

我有这个清除一行中的值的子元素:

Sub ClearRow_Click()
    Dim currRow As Long
    currRow = ActiveCell.Row
    If currRow >= 5 And currRow <= 300 Then
        Range("A" & currRow).ClearContents
    End If
End Sub

注意:currRow是被清除的行。

我想做的是包含一段代码，该代码将移动a列(长度直到第300行)中的所有以下值，从currRow (currRow + 1)之后的下一行开始进入currRow。所以在清除一行之后，我们有了这个:

Column A
1111
3333
4444

然后我希望代码在

之后执行此操作:

Column A
1111
3333
4444

我尝试过.End(xlDown)与Selection.cut，但我得到参考错误由于切割，因为我有其他列指的A，例如在B2我有:

=IF(A2<>"", $B$1, "")

结果是#REF!

Answer 1

这有点暴力，但我认为它得到了你想要的。

假设您的数据列位于列A:J中，并且您希望将 ActiveCell下方的所有行的值向上移动一行(有效地将ActiveRow替换为

)。

Option Explicit
Dim rng As Range
Dim shiftRng As Range
Sub ClearRow_Click()
    Dim currRow As Long
    Dim rng As Range
    Dim rngShift As Range
    currRow = ActiveCell.Row
    'This is the full range of data
    If rng Is Nothing Then Set rng = Range("A5:J300")
    'This gets the next n rows to the last row of your range
    If rngShift Is Nothing Then Set rngShift = Range("A" & (ActiveCell.Row + 1) & ":J300")
    If currRow >= 5 And currRow <= 300 Then
       '# This copies ONLY column A and moves the values up one row
       '  This will leave intact all of the rest of the data in columns B:J, etc.
       '  This preserves formatting -- essentially it just moves the values
        rngShift.Columns(1).Offset(-1).Value = rngShift.Columns(1).Value
        '# Get rid of the value in the last row, since it's been shifted up
        Range("A300").ClearContents
    End If
End Sub