时间戳

首页 > 解决方案 > Delete Row if populated with blanks and zeros within a range

excel - Delete Row if populated with blanks and zeros within a range

问题描述

I have a report where i need to remove the rows with no data in the cells or a zero within a range column C to O.

This code i have almost does it perfectly, but i found a fatal flaw in the logic. If the row has a positive and negative value that sum to zero it will be deleted while i would still need to retain that row.

I really appreciate the assistance from this site as I have been able to really automate many of my reports and help people in other departments! you guys rock! Thank You!

Dim rw As Long, i As Long
rw = Cells(Rows.Count, 1).End(xlUp).Row
For i = rw To 6 Step -1
If Application.Sum(Cells(i, 3).Resize(1, 17)) = 0 Then
Rows(i).Delete
End If
Next

标签： excelvbadelete-row

解决方案

不是检查 SUM，而是遍历每个单元格并检查其是否有效。

为了更好地解释这一点，我将为您使用伪代码：

  1. 创建一个标志变量并将其设置为 false
  2. 创建一个循环检查一行中的每个单元格
  3. 如果找到有效数字，则将标志设置为 true
  4. 在移动到下一个单元格之前，检查您的标志是否仍然为假
  5. 如果您的标志为假-> 继续下一个单元格
  6. 循环到行中所有单元格的末尾

伪代码变成粗代码

Dim rw As Long, i As Long
Dim rng As Range
Dim validRow As Boolean
validRow = false

rw = Cells(Rows.Count, 1).End(xlUp).Row

For i = rw To 6 Step -1
    Set rng = (Cells(i, 3).Resize(1, 17))
    For Each cell In rng
         If Not IsEmpty(cell) Then
            If cell.value <> 0 Then
               validRow = true
            End If
         End If
         If validRow = true Then
        Exit For
         End If
    Next cell
    If validRow = false Then
        Rows(i).Delete
    End If
    validRow = false
Next

[@LL 编辑：将 >0 更改为 <>0 也寻找任何不同于零的内容，以说明仅填充负值的行]

推荐阅读