0

I'm struggling with the following: Three columns in a worksheet, in column C, there's a variety of supplier names (supplier 1,2,3,4,5 etc..) and I'd like to use a combination of for..each with if statement to delete entire row if certain conditions are met. So far I've come up with the code below.

The code works as such, the problem is it only deletes a certain number of supplier records in the given range.E.g.if I have 20 records of "Supplier 1" in my table, the code deletes 10 of them and and I have to run the code manually again to delete the rest. Another problem is my range can be dynamic as well, maybe you could advise on this as well. I tried defining my range as the entire C column but this obviously doesn't make sense, long term. (and it didn't solve the problem of having to run the code multiple times anyway)

Sub peters()
Dim rng As Range
Dim cell As Range
Set rng = Sheet2.Range("C1:C37")
For Each cell In rng
If cell.Value = "Supplier 1" Or cell.Value = "Supplier 2" Then
cell.EntireRow.Delete
End If
Next cell
End Sub
Petersveter1499
  • 1
  • 1
  • When deleting Rows/Columns, it is best to do it starting from the last row/column. If you go down, since rows will go up after a row is deleted the new row in the location of the old deleted one will not be checked. (Hence the only half deleted rows in your case) – Vincent G Feb 10 '21 at 07:59
  • You might want to take a look here: https://stackoverflow.com/questions/7851859/delete-a-row-in-excel-vba/7862158#7862158 – Vincent G Feb 10 '21 at 08:03

1 Answers1

0

To keep the range dynamic you could use:

Set rng = Sheet2.Range("A1").CurrentRegion.Columns(3)

The correctness of the above will depend on how the data fields. CurrentRegion includes all adjacent populated cells.

FunThomas
  • 23,043
  • 3
  • 18
  • 34
MrT
  • 61
  • 4