Excel VBA 删除列中数字上方的一行

Question

我的 A 列中有数字。我正在尝试开发 VBA 代码来搜索 A 列中的每个数字，并在 A 列中有数字时删除上面的一行。示例:

A18 中的数字 -> 删除第 17 行

A21 中的数字 -> 删除第 20 行

A33 中的数字 -> 删除第 32 行

此代码用于在 A 列值上方插入一行。我尝试修改它以删除:

Dim r6 As Range, r7 As Range
Set printareaP = ThisWorkbook.Worksheets("Pricelist")

With printareaP.Range("Print_Area")


   For Each r6 In .Range("A1", .Range("A" & Rows.Count).End(xlUp))
    If Not IsEmpty(r6.Value) Then
        If r7 Is Nothing Then
            Set r7 = r6
        Else
            Set r7 = Union(r7, r6)
        End If
    End If
Next r6

If r7 Is Nothing Then
Else
    r7.EntireRow.Delete
End If

我收到错误:

Set r7 = Union(r7, r6)

----------------编辑------------------------------ -----------------

我想出了这段代码:

    With printareaP.Range("Print_Area")

    For Each Cell In .Range("A1", .Range("A" & Rows.Count).End(xlUp))
    If Not IsEmpty(Cell.Value) Then
        .Cells(Cell.Row - 1, 1).EntireRow.Delete
    End If
Next
End With

Answer 1

这是我的建议:

Option Explicit

Sub test()

Dim r6 As Range, r7 As Range
Dim ws As Worksheet
Dim LastRow As Long
Dim printareaP As Range, Cell As Range

Set ws = ThisWorkbook.Worksheets("Pricelist")



LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
Set printareaP = ws.Range(ws.Cells(1, 1), ws.Cells(LastRow, 1))

For Each Cell In printareaP
    If IsNumeric(Cell.Value) Then
        ws.Cells(Cell.Row - 1, 1).EntireRow.Delete
    End If
Next Cell


End Sub