当多列中的单元格为空白或为0时，删除整行 [英] Delete entire rows when cells in multiple columns are blank or 0

问题描述

我正在尝试编写一条代码，该代码基本上着眼于第13-33行，如果B-M列中的单元格全部为空白，而A列不是空白，则删除整个行.

I am trying to write a code which basically looks at rows 13-33 and deletes the entire row if the cells in Columns B-M are all Blank AND column A is NOT blank.

仅当B列中的单元格为空白时，我下面编写的第一个代码才删除整行，但为了删除整行，我需要将B-M中的所有单元格都为空白.

The first code which I have written below deletes the entire row only if the cell in Column B is blank but I need all the cells in B-M to be blank in order to delete the entire row.

Sub scheduleA()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Sheets("Schedule A Template").Select

Dim RowstoDelete As Long
x = 33
For RowstoDelete = Cells(x, 2).End(xlUp).Row To 13 Step -1

If (Cells(RowstoDelete, 2).Value = "0") And (Cells(RowstoDelete, 1).Value <> "") Then
        Rows(RowstoDelete).Delete Shift:=xlUp
    End If


Next RowstoDelete
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

我尝试在下面的代码中以不同的方式编写它，但是无法达到期望的结果.

I tried writing it differently as well in the following code but can't achieve the desire result.

Sub DeleteRows()


Dim i As Integer

For i = 33 To 13 Step -1
  If WorksheetFunction.CountA(Range("B" & i, "M" & i)) = 0 And WorksheetFunction.CountA(Range("A" & i)) <> "" Then
     Rows(i).EntireRow.Delete
    End If

Next i

End Sub

请帮助！

推荐答案

您要删除行的条件是:A列不为空白，B列至M列为空白.然后类似这样的事情就可以解决问题:

Your conditions for row deletion are: column A not blank, columns B to M blank. Then something like this should do the trick:

Sub ScheduleA()
    On Error GoTo errHandler

    Const TOP_ROW As Long = 13
    Const BOTTOM_ROW As Long = 33

    Dim rowIndex As Long

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    With ThisWorkbook.Worksheets("Schedule A Template")
        For rowIndex = .Cells(BOTTOM_ROW, "A").End(xlUp).Row To TOP_ROW Step -1
            If Not IsEmpty(.Cells(rowIndex, "A").Value2) Then '...column A is not blank.
                If Application.WorksheetFunction.CountA(.Range(.Cells(rowIndex, "B"), .Cells(rowIndex, "M"))) = 0 Then '...all cells on row rowIndex from columns B to M are blank.
                    .Rows(rowIndex).Delete Shift:=xlUp
                End If
            End If
        Next
    End With

Cleanup:
    On Error Resume Next
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    Exit Sub

errHandler:
    MsgBox Err.Description, vbExclamation + vbOKOnly, "Error"
    Resume Cleanup
End Sub

请注意， .Select 消失了；您几乎无需选择任何东西即可完成工作.不依赖于选择将使您的代码更加健壮.在上面的代码中，只要表达式以句点开头(例如 .Cells .)， With 块就会告诉其中的代码引用目标工作表.

Note that the .Select is gone; you almost never have to select anything to get the job done. Not relying on the selection will make your code much more robust. In the code above, the With block tells the code within it to refer to the target worksheet whenever an expression starts with a period, such as .Cells.

此外，当关闭 ScreenUpdating 和 Calculation 时，系统地包括错误处理以将其重新打开.这样，如果出现问题，您的代码将不会使Excel处于不良状态.

Also, when turning off ScreenUpdating and Calculation, systematically include error handling to turn them back on. This way, if something goes wrong, your code won't leave Excel in an undesirable state.

最后，您可以使用其CodeName直接引用它们，而不必通过其选项卡名称来引用工作表(如从Excel中看到的那样)，如VBA编辑器中所示，可以在属性"窗口中的工作表的(Name)"属性下进行引用.(按Ctrl + R以显示项目资源管理器"，单击"Microsoft Excel对象"节点下的工作表，然后按F4以显示属性"窗口).您可以更改此值.我通常将其更改为shtScheduleATemplate.然后， With 行可以改写为:

Finally, instead of referring to worksheets by their tab's name (as seen from Excel), you can refer to them directly using their CodeName, as seen from the VBA editor, in the Properties window, under the worksheet's (Name) property (press Ctrl+R to show the Project Explorer, click on the worksheet under the Microsoft Excel Objects node, then press F4 to display the Properties window). You can change this value; I'd typically change it to shtScheduleATemplate. Then, the With line could be re-written as:

With shtScheduleATemplate

...即使从Excel更改了工作表的名称，该操作仍然有效.

...which would still work even after you changed the worksheet's name from Excel.

编辑:在您问题的代码中，您在确定从哪个底部行索引开始循环时正在检查B列.但是，这样做可能会丢失一些应删除的行.我更改了答案，改为在A列中进行检查:

EDIT: in your question's code, you are checking column B when determining at which bottom row index to start the loop. However, by doing so, you may miss some rows that should be deleted. I've changed my answer to check within column A instead:

For rowIndex = .Cells(BOTTOM_ROW, "A").End(xlUp).Row To TOP_ROW Step -1

这篇关于当多列中的单元格为空白或为0时，删除整行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！