如果列AA = 0中的值，Excel宏将删除所有工作表上的所有行 [英] Excel macro to delete all rows on all worksheets if the value in column AA = 0

问题描述

我有一本包含197张的工作簿。如果列AA中的值为零，我需要删除每个工作表中的所有行。任何人都知道如何做到这一点？

解决方案

如果您想删除每一行，当且仅当该行的列AA为零，那么以下内容应该适用于您。

  Sub delete0rows（）
 Dim Worksheet作为Excel.Worksheet 
 Dim lastRow As Long 
 Dim i As Integer 
对于每个Worksheet在Application.ThisWorkbook.Worksheets 
 lastRow = Worksheet.Cells（Rows.Count，1）。结束（xlUp）.Row 
i = 1 
尽管i <= lastRow 
如果Worksheet.Range（AA& i）.Value = 0然后
工作表。行（i）。删除
i = i  -  1 
 lastRow = lastRow  -  1 
结束如果
i = i + 1 
循环
下一个
 End Sub 
  

注意，如果AA中的单元格值为0，则只会删除行。有几个微妙之处在这里Excel将显示一个0即使细胞val ue是'0或= 0等等，而这些行将不会被删除与上述代码。

I have a workbook containing 197 sheets. I need to delete all rows in each sheet if the value in column AA is zero. Anyone know how to do this?

解决方案

If you would like to delete each row if and only if that row's column AA is zero, then the below should work for you.

Sub delete0rows()
    Dim Worksheet As Excel.Worksheet
    Dim lastRow As Long
    Dim i As Integer
    For Each Worksheet In Application.ThisWorkbook.Worksheets
        lastRow = Worksheet.Cells(Rows.Count, 1).End(xlUp).Row
        i = 1
        Do While i <= lastRow
            If Worksheet.Range("AA" & i).Value = 0 Then
                Worksheet.Rows(i).Delete
                i = i - 1
                lastRow = lastRow - 1
            End If
            i = i + 1
        Loop
    Next
End Sub

Note this will only delete the row if the cell value in AA is 0. There are several subtleties here... Excel will show a 0 even if the cell value is '0 or =0 among other things, and those rows will not be deleted with the above code.

这篇关于如果列AA = 0中的值，Excel宏将删除所有工作表上的所有行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！