vba code help!! Loop to delete row

[seriouslystuck]

Hi All

I could really do with some help. Im trying to write a macro that searches a column and deletes rows depending on what value is in a cell. EG in column J I have 15k rows of aplha numeric codes, for example y20, 966, u22, y19.. I want to find the 966 and y20 and delete the rows but keep the u22 and y19.

I have got the code below but i dont know how to add another 'if not'. I do very little vba so excuse the novice approach.

Private Sub CommandButton1_Click()
Range("J1").Select
Do Until Selection.Value = ""
If Not Selection.Value = "y19" Then
Selection.EntireRow.Delete​

Else
Selection.Offset(1, 0).Select
End If
Loop​

Range("A1").Select
End Sub

Any help would be great

TIA

 

[Marcelo Branco]

Maybe this can help

http://www.rondebruin.nl/delete.htm

M.

 

[seriouslystuck]

Thanks for the link!

Will come in useful for another macro i need to write It doesnt explian how to delete everything but 2 values. In my original post column j my have approx 60 different types of codes and I want to delete them all except 4 or 5.

Apologies if my original post wasnt clear.

Thanks

 

[Marcelo Branco]

How many values and which do you want to keep?

M.

 

[seriouslystuck]

M

The values go from 1-999 then u10-u25, y10- y30, n10-n28 and are in random order throught the column. I want to keep rows with u22,y15 and n12 and delete the rest.

Thanks

 

[Marcelo Branco]

Assuming your data in column A beginning in row 1

Maybe this

The code below (adapted from Ron de Bruin's web page) deletes all rows whose values in column A are different than "u22", "y15" and "n12" (case sensitive)

Try it in a copy of your workbook

Sub Loop_Example()
    Dim Firstrow As Long
    Dim Lastrow As Long
    Dim Lrow As Long
    Dim CalcMode As Long
    Dim ViewMode As Long
 
    With Application
        CalcMode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With
 
    'We use the ActiveSheet but you can replace this with
    'Sheets("MySheet")if you want
    With ActiveSheet
        'We select the sheet so we can change the window view
        .Select
        'If you are in Page Break Preview Or Page Layout view go
        'back to normal view, we do this for speed
        ViewMode = ActiveWindow.View
        ActiveWindow.View = xlNormalView
        'Turn off Page Breaks, we do this for speed
        .DisplayPageBreaks = False
        'Set the first and last row to loop through
        Firstrow = 1
        Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
 
        'We loop from Lastrow to Firstrow (bottom to top)
        For Lrow = Lastrow To Firstrow Step -1
            'We check the values in the A column in this example
            With .Cells(Lrow, "A")
                If Not IsError(.Value) Then
                    If .Value <> "u22" And .Value <> "y15" And .Value <> "n12" Then .EntireRow.Delete
                    'This will delete each row with the Value <> "u22" and <> "y15" and <> "n12"
                    'in Column A, case sensitive.
                End If
            End With
        Next Lrow
    End With
 
    ActiveWindow.View = ViewMode
 
    With Application
        .ScreenUpdating = True
        .Calculation = CalcMode
    End With
 
End Sub

 

[seriouslystuck]

Thanks M

I cant read all the code as it seems to be too big for the box. Is this amount of code required?

Thanks

 

[seriouslystuck]

Hi

Is there a way to keep values begining with u,t and y and delete the rest

Thanks

 

[Marcelo Branco]

Hi

Is there a way to keep values begining with u,t and y and delete the rest

Thanks

Try this in a copy of your workbook

Remark: deletes rows whose values in column A do not begin with u, t or y

Sub Loop_Example()
    Dim Firstrow As Long
    Dim Lastrow As Long
    Dim Lrow As Long
    Dim CalcMode As Long
    Dim ViewMode As Long
 
    With Application
        CalcMode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With
 
    'We use the ActiveSheet but you can replace this with
    'Sheets("MySheet")if you want
    With ActiveSheet
        'We select the sheet so we can change the window view
        .Select
        'If you are in Page Break Preview Or Page Layout view go
        'back to normal view, we do this for speed
        ViewMode = ActiveWindow.View
        ActiveWindow.View = xlNormalView
        'Turn off Page Breaks, we do this for speed
        .DisplayPageBreaks = False
        'Set the first and last row to loop through
        Firstrow = 1
        Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
 
        'We loop from Lastrow to Firstrow (bottom to top)
        For Lrow = Lastrow To Firstrow Step -1
            'We check the values in the A column in this example
            With .Cells(Lrow, "A")
                If Not IsError(.Value) Then
                    If Not (.Value Like "u*") And Not (.Value Like "y*") _
                    And Not (.Value Like "t*") Then .EntireRow.Delete
                    'This will delete each row whose value in Column A
                    'does not begin with u , y or t
                End If
            End With
        Next Lrow
    End With
 
    ActiveWindow.View = ViewMode
 
    With Application
        .ScreenUpdating = True
        .Calculation = CalcMode
    End With
 
End Sub

To see the entire code use the scroll bar

M.