VBA to deal with row based on cell value then move to next row

[tommyleinen]

I have a sheet of data that needs each row to be dealt with a certain way. To assist with this I've added a helper column (U) which is a vlookup to a set of conditions. the cell value from U10 downwards returns a value 1-6. The formula gets pasted into column U, so the next blank cell (usually around 50 - 100 rows down), is where the data ends and this can vary.

1 means delete row
2 means IF L>0 Then clear contents of M and N
3 means IF L>N then make N the value of L, and clear contents of L and M
4 means clear contents of L and M
5 means delete row unless sheetname is Sheet4
6 means clear contents of L and N

I think I can put together the code for 1, apart from making the new active cell to be U of the row after the one deleted.
2-6 look within my capability

Really I need help with 1 and looping through the rows until the next cell is blank.

Any help would be appreciated

 

[Joe4]

When deleting or inserting rows, it is always better to work from the bottom of the sheet up. That way you aren't affecting the range you have not checked yet (and missing or duplicating any rows).

So what I typically like to do is find the last row in column U with data, and work up from there in a loop, structured like this:

Dim lr as Long
Dim r as Long

'Find last row with data in column U
lr = Cells(Rows.Count, "U").End(xlUp).Row

'Loop through all rows backwards
For r = lr to 1 Step -1
'   Check the value in column U and make decision on what to do
    Select Case Cells(r, "U").Value
        Case 1
            'Do your steps here for value of 1 here
        Case 2
            'Do your steps here for value of 2 here
       ...
        Case 6
            'Do your steps here for value of 1 here
    End Select   
Next r

If you aren't familiar with CASE statements, see here: MS Excel: How to use the CASE Statement (VBA)

 

[tommyleinen]

Thankyou for your insight into this, I believe you have saved me a lot of frustration using your 'bottom up' strategy.

All working as intended, thankyou very much!!!

 

[Joe4]

You are welcome.
Glad I was able to help!