求助：VBA删除D:H全0行代码失效，如何修改lastrow2及代码？

The issue with your code is that you're using column C to determine the last row to check (lastrow2 = Cells(Cells.Rows.Count, "C").End(xlUp).Row). This means any rows where column C is empty (even if D:H are all zero) won't be included in your loop—so those rows never get checked for deletion, especially when there are consecutive empty cells in C.

The Fix: Calculate Last Row Based on Columns D:H #

Instead of relying on column C, you need to find the last row that has data (including zeros) in any of columns D through H. This ensures all rows with values in D:H are checked, regardless of whether column C is empty or not.

Corrected Code Snippet #

Sub DeleteRowsWithAllZeros()
    Dim lastrow2 As Long
    Dim j As Long
    
    ' Get the last row with data in columns D to H
    lastrow2 = Cells(Rows.Count, "D").End(xlUp).Row
    lastrow2 = Application.Max(lastrow2, Cells(Rows.Count, "E").End(xlUp).Row)
    lastrow2 = Application.Max(lastrow2, Cells(Rows.Count, "F").End(xlUp).Row)
    lastrow2 = Application.Max(lastrow2, Cells(Rows.Count, "G").End(xlUp).Row)
    lastrow2 = Application.Max(lastrow2, Cells(Rows.Count, "H").End(xlUp).Row)
    
    ' Loop from bottom to top to avoid skipping rows after deletion
    For j = lastrow2 To 1 Step -1
        ' Check if all cells in D(j) to H(j) are exactly 0
        If (Cells(j, 4) = 0 And Cells(j, 5) = 0 And Cells(j, 6) = 0 And _
            Cells(j, 7) = 0 And Cells(j, 8) = 0) Then
            Rows(j).Delete
        End If
    Next j
End Sub

Key Notes #

• Why this works: By taking the maximum last row across D:H, we ensure every row with data in those columns is included in the loop—even if column C is empty.
• Loop direction: You were already doing this correctly, but looping from the last row up to 1 is critical when deleting rows. If you looped top to bottom, deleting a row would shift the rows below up, causing you to skip the next row.
• Empty vs. Zero: This code only deletes rows where all cells in D:H are exactly 0. If you want to treat empty cells as 0 (and delete rows with a mix of zeros and empty cells), you could replace the If condition with WorksheetFunction.Sum(Range(Cells(j,4), Cells(j,8))) = 0—but that's a slight deviation from your original requirement.

内容的提问来源于stack exchange，提问作者Bamane