Delete rows from named range with some condition

Question

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.

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:

Set Rng = iList.[Table] I need to clean it

  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

Answer 1

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

Answer 2

I am not sure, but you might look for this

If ThisWorkbook.Names("ID").RefersToRange.Value = "0" Then

replace ThisWorkbook with any workbook or worksheet object you like.

If this is not it, please comment what you want to be checked for "0", because I am not sure if I understood you correctly.

edit

now, I know what is going on - you have a problem like this here

Vba macro to copy row from table if value in table meets condition

and would need code something like this here

Dim rngFiltered as range
Rng.AdvancedFilter xlFilterCopy, Tabelle1.Range("CRITERIA"), rngFiltered, False

lst.InsertRowRange.Resize(Rng.Rows.Count).Value = rngFiltered.Value

whereby "CRITERIA" is the name of a range like "G1:G2", with G1="Title of Column D" G2=">0"

I would edit you code to this, if it is always the same cell:

  Dim rangeRow As Range
  Dim rowCell As Range

  Set Rng = Tabelle1.Range("A1:A50")

  For Each rangeRow In Rng.Rows

    Set rowCell = rangeRow.Cells(1, 2)
    If rowCell.value = "0" Then
      rangeRow.Delete
    End If
  Next rangeRow

or to this if you need to check all cells

  For Each rangeRow In Rng.Rows
    For each rowCell in rangeRow           
       If rowCell.value = "0" Then
        rangeRow.Delete
        exit for
       End If
    next rowCell
  Next rangeRow