删除行（单元格）

"开始"

Sub TheBeginning()
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim ws As Worksheet: Set ws = wb.Sheets("Sheet1")
    Dim fCell As Range: Set fCell = ws.Range("O2")
    Dim rg As Range
    Set rg = ws.Range(fCell, ws.Cells(ws.Rows.Count, fCell.Column).End(xlUp))

    ' Empty the cells containing zeros.        
    rg.Replace What:="0", Replacement:="", LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

    ' add the code of the preferred option here...

End Sub

备选案文1

• 删除列O中的空单元格行，无论列N中有什么。

Sub Option1()
    
    ' Delete rows of empty cells in column 'O' no matter what's in column 'N'.
    
    Dim vrg As Range
    On Error Resume Next ' prevent error if no cells
        Set vrg = rg.SpecialCells(xlCellTypeBlanks)
    On Error GoTo 0
    If vrg Is Nothing Then Exit Sub
    
    Dim drg As Range: Set drg = Union(vrg.Offset(, -1), vrg)
    
    drg.Select ' .Delete xlShiftUp
    
End Sub

备选案文2

• 删除NANDO列中的空单元格行。

Sub Option2()
    
    ' Delete rows of empty cells in columns 'N' AND 'O'.
    
    Dim drg As Range: Set drg = Union(rg.Offset(, -1), rg)
    
    Dim vrg As Range
    On Error Resume Next ' prevent error if no cells
        Set vrg = drg.SpecialCells(xlCellTypeBlanks)
    On Error GoTo 0
    If vrg Is Nothing Then Exit Sub
    
    Dim ivrg As Range
    On Error Resume Next ' prevent error if no cells
        Set ivrg = Intersect(drg, Intersect(vrg, drg.Columns(1)).EntireRow, _
            Intersect(vrg, drg.Columns(2)).EntireRow)
    On Error GoTo 0
    If ivrg Is Nothing Then Exit Sub
    
    ivrg.Select ' .Delete xlShiftUp
    
End Sub

选项3错误

• 删除列NORO中的空单元格行（错误）。我标记错误，因为它将单独删除每列中的单元格，我认为这是非常不可能的，虽然这是一个有效的选项。

Sub Option3Wrong()
    
    ' Delete rows of empty cells in columns 'N' OR 'O' (WRONG).
    
    Dim drg As Range: Set drg = Union(rg.Offset(, -1), rg)
    
    Dim vrg As Range
    
    On Error Resume Next ' prevent error if no cells
        Set vrg = drg.SpecialCells(xlCellTypeBlanks)
    On Error GoTo 0
    
    If vrg Is Nothing Then Exit Sub
    
    vrg.Select ' .Delete xlShiftUp
    
End Sub

备选案文3

• 删除NORO列中的空单元格行。

Sub Option3()
    
    ' Delete rows of empty cells in columns 'N' OR 'O'.
    
    Dim drg As Range: Set drg = Union(rg.Offset(, -1), rg)
    
    Dim vrg As Range
    On Error Resume Next ' prevent error if no cells
        Set vrg = drg.SpecialCells(xlCellTypeBlanks)
    On Error GoTo 0
    If vrg Is Nothing Then Exit Sub
    
    Set vrg = Intersect(vrg.EntireRow, drg)
    
    vrg.Select ' .Delete xlShiftUp
    
End Sub

提示

• 注意在单元格被移动后 ISBLANK 列是如何变得混乱的。这是在不删除整行时要记住的。
• 在开发删除代码时，建议使用Select而不是Delete。当然，您需要记住，工作表需要处于活动状态才能在此简单表单中使用它。您可以使用以下方法改进它：

If not wb Is ActiveWorkbook Then wb.Activate
If Not ws Is ActiveSheet Then ws.Select