从某个条件删除命名范围的行 [英] Delete rows from named range with some condition

问题描述

在我之前的帖子中：从所有的数据某些表的书籍我已经问过如何从EXCEL中的所有打开的书籍中获取一些数据。

In my previous post: Take data from all books to some table I have asked how to get some data from all opened books in EXCEL.

但是有一些问题。我不知道如何检查命名范围中列名Id的值是否为0。如果有的话，它不应该添加到最终表中。我可以这样做吗？

But there is some problem. I don't know how to check if a value of column name "Id" in named range has value "0". If it has, it shouldn't be added into final table. How can I do this?

所以在我得到命名范围值后：

so after I get named range value:

设置Rng = iList。[表] 我需要清理它

  i = 1
         For Each row In Rng.Rows


      Set cell = row.Cells(1, 2)
      If cell = "0" Then

      Rng.Rows(i).Delete

      End If

      i = i + 1


      Next

推荐答案

这是一个我一直在努力，但这段代码工作，我认为应该工作你想做什么在这种情况下，我正在使用一个库存号（Inv）的命名范围，并查找它来找到小于26的任何值。如果存在，我删除该行。当循环完成后，我重新建立最后一行（这将改变，假设我已经删除了一些行）。您可以替换您的命名范围并将IF语句中的条件更改为value = 0：

This is one I have struggled with but this code works, and I think should work for what you're trying to do. In this case I'm using a named range for an inventory number ("Inv") and looking through it to find any values less than 26. If those exist, I delete the row. When the loop is finished, I re-establish the final row (which will change, assuming I've deleted some rows). You can substitute your Named Range and change the condition in the IF statement to value = 0:

Set rngCells = Range("Inv")
For i = rngCells.Cells.Count To 0 Step -1
    If rngCells(i).Value < 26 Then
        rngCells(i, 1).EntireRow.Delete
    End If
Next i

FinalRow = Cells(Rows.Count, 2).End(xlUp).Row

这篇关于从某个条件删除命名范围的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！