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