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

score 1 · Answer 1 · answered Dec 18 '12 at 22:13

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

score 0 · Answer 2 · edited May 23 '17 at 11:49

0

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

edited May 23 '17 at 11:49

Community

1
1

answered Sep 20 '12 at 12:44

Jook

4,564
3
26
53

here: lst.InsertRowRange.Resize(Rng.Rows.Count).Value = Rng.Value I add all rows, but I need to check my condition and add only that rows that pass it – revolutionkpi Sep 20 '12 at 12:47
ok, so you basically want a matrix like range filtered to result in antoher matrix like range, which has only data that passed this filter. in that case you should edit your initial question, because it is very misleading. – Jook Sep 20 '12 at 12:57
ok, can you help to delete rows that not pass my condition from named range? – revolutionkpi Sep 20 '12 at 13:00
my guess here would be, that you have to eighter create your rang without those rows or you would have loop throug them and move the good values into an array, then use this array for InsertRowRange – Jook Sep 20 '12 at 13:05
edited my answer, there sure is room to optimize this, but it should work as starting point – Jook Sep 20 '12 at 13:39
please be more specific, my example worked, i'll guess the CRITERIA part is the problem, but you gotta give more informations - i am not infront of your table ;) – Jook Sep 20 '12 at 13:59
in my case I don't know where I have 0 value of cell – revolutionkpi Sep 20 '12 at 14:01
i thought it should always be in the column with "ID" as label? and by that i mean Cells(1,1).value = "ID", if this column would be column "A". However, if there are multiple columns, you could extend your criterias to cover them too. – Jook Sep 20 '12 at 14:28
maybe this will help you to adjust the filter: http://www.contextures.com/xladvfilter01.html – Jook Sep 20 '12 at 14:32
gave it an edit, tested this, works. if not i want to know what is behind `Set Rng = iList.[Table]` - because if this is not an unsual RANGE object, there might be the trouble – Jook Sep 20 '12 at 15:58

Delete rows from named range with some condition

2 Answers2

Linked