Home > Software engineering >  Loop through a range and delete row if certain condition is met
Loop through a range and delete row if certain condition is met

Time:11-05

I am a little bit confused, I was trying to loop through a range of cells, and check if this cell value equals other value stored in the variable, if not, I want to delete the whole row. It is starting with last row in the range, but when I run this macro, it stops after first value, and I want to keep on looping through other values. If I will manually run it again, it will work, but then it will stop on the first cell where cell.value = id1 or id2. Is there a "else continue" in vba? or else next cell? I was trying to play with this and googling it, but no luck.

The IDs values I had in my example was just; 3 & 4. While range of values I was looping through was just 1 to 6

#as suggested below when I changed ws2.Range("A" & LastRow) to a ws2.Range("A1:A" & LastRow) it kind of works, but it doesnt loop through all cells at once, but I need to run this macro twice... to remove all unwanted cells... this is confusing

Sub DeleteRows()
 
Dim cell As Range
Dim wb As Workbook: Set wb = ThisWorkbook
Set ws = wb.Sheets("ID_value_source")
Set ws2 = wb.Sheets("Sheet2")
Dim LastRow As Long

id1 = ws.Range("A2").Value
id2 = ws.Range("A3").Value

ws2.Activate
LastRow = ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Row

For Each cell In ws2.Range("A" & LastRow)
    If cell.Value <> id1 And cell.Value <> id2 Then
        cell.EntireRow.Delete
    End If
Next cell
    
End Sub

Thanks eM

CodePudding user response:

update - based on the feedback & help below I managed to solve this, thank you all; this is the code that works for me:

Sub del_Rows()

Dim wb As Workbook: Set wb = ThisWorkbook
Set ws = wb.Sheets("ID_value_source")
Set ws2 = wb.Sheets("Sheet1")
Dim LastRow As Long

id1 = ws.Range("A2").Value
id2 = ws.Range("A3").Value

LastRow = ws2.Range("A" & ws2.Rows.Count).End(xlUp).Row

For i = LastRow To 2 Step -1
    If ws2.Cells(i, 1).Value <> id1 And ws2.Cells(i, 1).Value <> id2 Then ws2.Rows(i & ":" & i).EntireRow.Delete
Next i

End Sub
  •  Tags:  
  • vba
  • Related