CODEWITHSUNDEEP
excelvbaexcel-formulaexcel-2010
code3
code3

Reputation: 91

Get all the rows meeting certain conditions

I have below sample data in excel:

Row ColA ColB
1   10   10
2   0    10
3   50   0
4   70   70
5   0    0

I would like to keep all the rows where A != 0 AND A !=B. In my example, I need to only keep row 3. I need to delete row 1 and row4 because A = B. I need to delete row 2 and row5 because A = 0. from previous research, the easiest way is to use a new column (say colC) and mark colC "delete" for rows meeting condition A = 0 or A = B. Then delete all the rows where colC has "delete". What's the easier way to do so? I tried formula but didn't work for me. Tried VBA but didn't work either (I might be using them wrongly as I am new to Excel).Thanks!

Upvotes: 0

Views: 3018

Answers (1)

Harun24hr
Harun24hr

Reputation: 37155

Formula solution to show result as per condition.

=FILTER(A2:A6,(A2:A6<>0)*(A2:A6<>B2:B6))

enter image description here

VBA solution to delete rows.

Sub RemoveMatchRow()
Dim rng As Range, rws As Long, i As Long
Dim LastRow As Long

  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Set rng = ActiveSheet.Range("A2:A" & LastRow)
  rws = rng.Rows.Count
  
  For i = rws To 1 Step (-1)
    If (rng.Rows(i) = 0) Or (rng.Rows(i) = rng.Rows(i).Offset(, 1)) Then
        rng.Rows(i).EntireRow.Delete
    End If
  Next
  
End Sub

Upvotes: 3

Related Questions