Loop and delete rows minus one if no value is in the cell.

[naryfa]

Hello again,

So this one is beyond the capabilities of what I've learned so far. I googled around and didn't find much. I need for the macro to look into column D, and if it finds a value in column D, delete the rows down to the next value minus two rows, so that the line with first and last names above is preserved. To put it simply, red has to be deleted. The sample will show why:

A	B	C	D	E
470	FIRSTNAME, LNAME
	14:30-16:30 <tbody> </tbody>	XYZ	123	ONE
308	FIRNAME, LANAME
	06:38-08:15 <tbody> </tbody>
504 <tbody> </tbody>	FNAME, LASTNAME
	06:38-08:15 <tbody> </tbody>
519	FNAME, LNAME
	07:47-10:30 <tbody> </tbody>
	10:30-12:45 <tbody> </tbody>	>A/D< <tbody> </tbody>	254	FIVE
	12:45-15:30 <tbody> </tbody>
	15:30-17:30d <tbody> </tbody>	>A/D< <tbody> </tbody>	254	FIVE

<tbody>
</tbody>

I know they say that deleting rows should be done from the bottom up. So maybe it will be easier to search column D from the bottom, and then skip 2 rows up upon finding a value.

But then again, if two lines are skipped, another value may pop in, like in the sample above. You find 254, and two lines up, there's another 254, and both have to be preserved.

Please tell me if this, can at all, be done.

 

[madaknarf]

So here is a start. This code checks column B for a value, then checks C,D, and E. If there are values in B but nothing in C,D and E then it deletes the row, otherwise it moves on. It may not be the exact answer you're needing but it's a start and you should be able to alter/add to this to make is suit your situation. Good luck!

Sub del_rows()


Dim i As Integer
i = 1


Do Until i = 50

If ActiveSheet.Range("B" & i).Value <> "" Then
If ActiveSheet.Range("C" & i).Value = "" And ActiveSheet.Range("D" & i).Value = "" And ActiveSheet.Range("E" & i).Value = "" Then
ActiveSheet.Rows(i).EntireRow.Delete
i = i - 1
End If
i = i + 1
Else
i = i + 1
End If


Loop




End Sub

 

[naryfa]

Alright, that does help a lot, I admit. Just how do I make it run from the bottom up, and skip 2 lines after it finds something.