vba - 删除自定义组而不是整个行或列中的单元格

我有这段代码，它按整列删除单元格。最初，我认为这样做会很有用，后来我意识到，如果这样做，同一列上的有用数据也将被删除。因此，我需要一个代码，该代码可让我根据自己的需要删除任何范围的单元格，无论是行还是列

Sub TestDatabase()

Dim rng As range, rngError As range, delRange As range
Dim i As Long, j As Long, k As Long
Dim wks As Worksheet

On Error Resume Next

Set rng = Application.InputBox("Select cells To be deleted", Type:=8)

On Error GoTo 0

If rng Is Nothing Then Exit Sub Else rng.delete

For k = 1 To ThisWorkbook.Worksheets.Count 'runs through all worksheets

  Set wks = ThisWorkbook.Worksheets(k)

  With wks

    For i = 1 To 7 '<~~ Loop trough columns A to G

        '~~> Check if that column has any errors
        On Error Resume Next

        Set rngError = .Columns(i).SpecialCells(xlCellTypeFormulas, xlErrors)

        On Error GoTo 0

        If Not rngError Is Nothing Then
            For j = 1 To 100 '<~~ Loop Through rows 1 to 100
                If .Cells(j, i).Text = "#REF!" Then
                    '~~> Store The range to be deleted
                    If delRange Is Nothing Then
                        Set delRange = .Columns(i)
                        Exit For
                    Else
                        Set delRange = Union(delRange, .Columns(i))
                    End If
                End If
             Next j
         End If

     Next i

  End With

Next k

'~~> Delete the range in one go
If Not delRange Is Nothing Then delRange.delete

End Sub

最佳答案

我认为，为了满足您的需求，您只需要删除.Columns()方法。反过来，这又带来了新的问题-删除单元格时，Excel应该以哪种方式移动其余单元格？

您可以尝试这样的事情：

If .Cells(j, i).Text = "#REF!" Then
    '~~> Store The range to be deleted
    If delRange Is Nothing Then
        Set delRange = .Cells(j, i)
        '// Exit For <~~ This stops your code adding any more cells to delRange
    Else
        Set delRange = Union(delRange, .Cells(j, i))
    End If
End If

然后删除时：

'// Could use xlUp, xlDown, xlToRight or xlToLeft
delRange.delete shift:=xlUp

值得注意的是，由于delRange现在指向一组单元格，因此可以删除：


仅使用上述方法的单元格。
使用delRange.EntireRow.Delete shift:=xlUp属于这些单元格的行
或使用delRange.EntireColumn.Delete shift:=xlToLeft属于这些单元格的列