deleting rows based on info in certain cells

[rtw]

I have a column that has different SIC codes in it (00045, 00046, etc). I would like to set up a macro or filter that would take cells in this column and delete each row that contains a certain code that I don't want.

Example, I want to delete all the rows with SIC codes that have 00345, 00873, 00145, etc in a particular cell. I would like a macro that would look at each cell in the column and delete the rows that I don't want.

Any advise is greatly appreciated.

THanks
Rusty

 

[VoG]

Hello and welcome to MrExcel.

Assuming column A and that those values are text rather than numbers try

Sub DelRows()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = LR To 1 Step -1
    With Range("A" & i)
        If .Value = "00345" Or .Value = "00873" Or .Value = "00145" Then .EntireRow.Delete
    End With
Next i
End Sub

 

[crimson_b1ade]

you can also research advanced filters. see MrExcel podcast to get you started: http://www.mrexcel.com/Excel_Filtering_Criteria_training.html

you may pick up some tips and pointers that'll will help you solve your problem.

 

[B-Art]

The following code first searches for value 0 in column B, making a string of ranges, from row 3 to LastRow. So You can have a Upper and Lower Limit
Then in the second fase it delete all the rows found.
Rw is used First as a Integer an Second as a string (a Variant is sometimes for lazy poeple

    Dim Rw, Rw2Del, RwMem, Rw0 As Boolean, RwSel As String: RwMem = 1: Rw0 = False
    
    For Rw = 3 To LastRow
        Select Case Range("B" & Rw).Value
        Case 0: If Not Rw0 Then Rw0 = True: RwSel = RwSel & Rw - Rw2Del & ":" Else: Rw2Del = Rw2Del + 1
        Case Else: If Rw0 Then Rw0 = False: RwSel = RwSel & Rw - RwMem & ";": Rw2Del = Rw2Del + 1: RwMem = RwDel + 1
        End Select
    Next Rw
    RwSel = Left(RwSel, Len(RwSel) - 1)
    For Each Rw In Split(RwSel, ";")
        ActiveSheet.Range(Rw).Select
        Selection.Delete xlShiftUp
    Next Rw
    
    LastRow = LastRow - Rw2Del

At the end LastRow minus Rw2Del Changes LastRow to the correct Value, so you can use it in the following code.

You can add more Case statements to your liking, lets say also delete Value "B"

        Case "B": If Not Rw0 Then Rw0 = True: RwSel = RwSel & Rw - Rw2Del & ":" Else: Rw2Del = Rw2Del + 1

Case Else Must Always be there to give a value after the ":"

 

[B-Art]

This Code is more readable.

The following code first searches for value 0 in column B, making a string of ranges, from row 3 to LastRow. So You can have a Upper and Lower Limit
Then in the second fase it delete all the rows found.
Rw is used First as a Integer an Second as a string (a Variant is sometimes for lazy poeple

    Dim Rw, Rw2Del, RwMem, Rw0 As Boolean, RwSel As String: RwMem = 1: Rw0 = False
    
    For Rw = 3 To LastRow
        Select Case Range("B" & Rw).Value
        Case 0
            If Not Rw0 Then
                Rw0 = True
                RwSel = RwSel & Rw - Rw2Del & ":"
            Else
                Rw2Del = Rw2Del + 1
            End If
        Case Else
            If Rw0 Then Rw0 = False
                RwSel = RwSel & Rw - RwMem & ";"
                Rw2Del = Rw2Del + 1
                RwMem = RwDel + 1
            End If
        End Select
    Next Rw
    RwSel = Left(RwSel, Len(RwSel) - 1)
    For Each Rw In Split(RwSel, ";")
        ActiveSheet.Range(Rw).Select
        Selection.Delete xlShiftUp
    Next Rw
    
    LastRow = LastRow - Rw2Del

At the end LastRow minus Rw2Del Changes LastRow to the correct Value, so you can use it in the following code.

You can add more Case statements to your liking, lets say also delete Value "B"

        Case "B"
           If Not Rw0 Then
               Rw0 = True
               RwSel = RwSel & Rw - Rw2Del & ":"
           Else
               Rw2Del = Rw2Del + 1
           End If

Case Else Must Always be there to give a value after the ":"

 

[Jonmo1]

B-Art, Are you sure you're responding to the right thread??

Your response doesn't appear to have anything to do with the posted question...

 

[Peter_SSs]

This Code is more readable.

    Dim Rw, Rw2Del, RwMem, Rw0 As Boolean, RwSel As String: RwMem = 1: Rw0 = False
    
    For Rw = 3 To [COLOR="Blue"]LastRow[/COLOR]
        Select Case Range("B" & Rw).Value
        Case 0
            If Not Rw0 Then
                Rw0 = True
                RwSel = RwSel & Rw - Rw2Del & ":"
            Else
                Rw2Del = Rw2Del + 1
            End If
        Case Else
            [COLOR="DarkOrange"]If Rw0 Then Rw0 = False[/COLOR]
                RwSel = RwSel & Rw - RwMem & ";"
                Rw2Del = Rw2Del + 1
                RwMem = [COLOR="Red"]RwDel [/COLOR]+ 1
            [COLOR="SeaGreen"]End If[/COLOR]
        End Select
    Next Rw
    RwSel = Left(RwSel, Len(RwSel) - 1)
    For Each Rw In Split(RwSel, ";")
        ActiveSheet.Range(Rw).Select
        Selection.Delete xlShiftUp
    Next Rw
    
    LastRow = LastRow - Rw2Del

May be it's easier to read, but did you actually test it?

A few things I've noticed

1. LastRow is undefined.

2. RwDel is undefined.

3. The green End If has no matching 'If' statement since the 'If' statement above it is terminated by the Rw0 = False statement on the same line.

In any case, it would seem simpler to me to just filter the values you didn't want and delete them all at once instead of all that looping through rows, building strings, splitting them again etc.

 

[B-Art]

May be it's easier to read, but did you actually test it?

A few things I've noticed

1. LastRow is undefined. (You can give it a value yourself)

2. RwDel is undefined. (I change the code after pasting so it should read Rw2Del)

3. The green End If has no matching 'If' statement since the 'If' statement above it is terminated by the Rw0 = False statement on the same line.
(I forgot a newline after then) (if you choose the compressed coded version it should work ok)

In any case, it would seem simpler to me to just filter the values you didn't want and delete them all at once instead of all that looping through rows, building strings, splitting them again etc.

B-Art, Are you sure you're responding to the right thread??

Your response doesn't appear to have anything to do with the posted question...

It is about deleting rows on the basis of cell value, so what am I missing here

 

[B-Art]

May be it's easier to read, but did you actually test it?

A few things I've noticed

1. LastRow is undefined.

2. RwDel is undefined.

3. The green End If has no matching 'If' statement since the 'If' statement above it is terminated by the Rw0 = False statement on the same line.

In any case, it would seem simpler to me to just filter the values you didn't want and delete them all at once instead of all that looping through rows, building strings, splitting them again etc.

How would you use filter in this case? I would like to see your code doing the seem thing.

 

[Peter_SSs]

How would you use filter in this case? I would like to see your code doing the seem thing.

Well, for the example your code was attempting to do - remove all rows where the column B value is 0, I would use this.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> DelRows()<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> Range("B1", Range("B" & Rows.Count).End(xlUp))<br>        .AutoFilter Field:=1, Criteria1:="0"<br>        .Offset(1).EntireRow.Delete<br>        .AutoFilter<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>