find value and delete next row

[lhardee]

Hello all,


I am in need of some expertise on this board. I am trying to delete certain rows depending on what values are found in column C. Listed in column C are many values like AC68. I would like to delete rows with that value found in column C with the exception of any "following" rows when AC68U is found. I've attached a spreadsheet I hope is more helpful that the way I'm describing it.

Any help would be apprecated.

Thanks,

lhardee

[mdmackillop]

Is this what you want?
Sub Macro1()
Dim rw
For rw = ActiveSheet.UsedRange.Rows.Count To 1 Step -1
If Cells(rw, 3) <> "OC48" Then
Cells(rw, 3).EntireRow.Delete
End If
Next
End Sub

[lhardee]

Hi mdmackillop,

I made a mistake in my post. OC48 is what I want to keep, but, only the instances where they are directly under OC48U. Other rows not accompanied by OC48U I would like to have deleted.

Your code did exactly what I needed, but it will keep all rows with OC48 found in column C.

Thanks!

lhardee

[mdmackillop]

Sub Macro1()
Dim rw
Rows(1).Insert
For rw = ActiveSheet.UsedRange.Rows.Count To 2 Step -1
If Not (Cells(rw, 3) = "OC48" And Cells(rw - 1, 3) = "OC48U") Then
Cells(rw, 3).EntireRow.Delete
End If
Next
Rows(1).Delete
End Sub

[lhardee]

Thanks mdmackillop,

Can I bother you with one more change? : pray2: On the same sheet, there are other values that I don't want to delete. Is there a way to say ignore everything in column C (don't delete the rows) exept for OC48U. When OC48U is found, delete that row and keep the row below it.

lhardee

[david000]

You may have answered your own question. Can you go back to your example workbook, and filter what you need to delete?

[lhardee]

Hi David2000,


Filter almost does the trick. Thank you !!! The problem is that there are equal values in column C that I want to delete. The only factor that separates the values I want to keep and the values I don't are that OC48U is directly above what I want.

The trouble with mdmackillop's code is that it gives me exactly what I asked for....only I wasn't specific enough. :banghead: There are other values in column C that I want to keep.

I've attached a better example.....hopefully.



lhardee

[mdmackillop]

You have not given any logic to distinguish between keep/delete, unless you want code to delete rows with no green cells. If you cannot clearly describe the logic to use, how can we come up with it?

[lhardee]

mdmackillop,

First of all, you guys are great and thanks for taking the time to help me. It is really appreciated as I know your time is valuable.

Sorry for being a bit confusing and not providing enough info. I'll try to be a bit more specific.

There are maybe 10 different values that wind up being displayed in column C.

My ultimate goal is to identify what is in column C and delete all rows that I don't need. There are two instance where values OC48 and OC192 are needed (only when trailing after OC48U or OC192U). Other instances of OC48 or OC192 I would like to delete.

Maybe something like:



Sub FilterGroup()

Dim LastRow As Long, i As Long, cel As Range

LastRow = Range("C65536").End(xlUp).Row

For i = LastRow To 1 Step -1

If Range("C" & i).Value = "DWD" Or Range("C" & i).Value = "T4XU" Or _
Range("C" & i).Value = "DWDMU" Then

'listing out all values I don't want to delete



To keep what I don't want to delete. Then code that is conditional for OC48 and OC192.

I hope this is more to the point.

Thanks again

lhardee