CODEWITHSUNDEEP
excelvba
phillipsK
phillipsK

Reputation: 1516

For loop not catching all intended values

Dim p As Variant
For Each p In Range("P2:P8")
    If p.Value = "TGL block new" Then

        p.EntireRow.Select
        Selection.Delete Shift:=xlUp


        End If
   Next p

How come this for loop is not catching all the TGL block new values? If any row contains this value in Column P2:to last cell, the entire horizontal row needs to be deleted from the data range which is A2:BL6

Upvotes: 0

Views: 51

Answers (1)

user4039065
user4039065

Reputation:

You are losing the position of p because you delete it and then attempt to shift to the next one when you are already at the next one. A general rule of thumb when deleting rows is to start at the bottom and work up but that will mean adjusting your method.

Dim p As long
For p=8 to 2 step -1
    If lcase(trim(cells(p, "P").Value)) = "tgl block new" Then
        cells(p, "P").EntireRow.Delete Shift:=xlUp  ' the Shift:=xlUp isn't needed beyond a visual reminder
    End If
Next p

I've added some error control on the value to remove leading/trailing spaces and case-insensitive.

Upvotes: 1

Related Questions