Consulting

Results 1 to 9 of 9

Thread: Delete certain rows

  1. #1

    Delete certain rows

    I need to search through an entire column. If the values E, A, F, or P are found in that column I need to delete the entire row.

    On another sheet I need to delete the rows if they DO NOT have those values. Any help is appreciated.

    Thanks!

  2. #2
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    Hi,

    Would something like this do you ..


    Option Explicit
    Sub forHondaTiger()
        Dim lastRow As Long, i As Long, cel As Range
        lastRow = Range("A65536").End(xlUp).Row
        For i = lastRow To 1 Step -1
        If Range("A" & i).Value = "E" Or Range("A" & i).Value = "A" Or _
            Range("A" & i).Value = "F" Or Range("A" & i).Value = "P" Then
            Range("A" & i).EntireRow.Delete
        End If
        Next i
    End Sub

  3. #3
    VBAX Regular
    Joined
    Jun 2004
    Location
    Denmark
    Posts
    58
    Location
    Ok, I'm a bit to slow but here it is anyway
    Place the cursor in the column you wish to search and play the appropiate makro


    Sub DeleteAEFP()
    Dim test As Boolean, x As Long, lastrow As Long, col As Long
    col = ActiveCell.Column
    lastrow = Cells(65536, col).End(xlUp).Row
    For x = lastrow To 1 Step -1
        test = Cells(x, col).Text Like "[AEFPaefp]"
        If test = True Then Cells(x, col).EntireRow.Delete
    Next
    End Sub
     
     
    Sub LeaveOnlyAEFP()
    Dim test As Boolean, x As Long, lastrow As Long, col As Long
    col = ActiveCell.Column
    lastrow = Cells(65536, col).End(xlUp).Row
    For x = lastrow To 1 Step -1
        test = Cells(x, col).Text Like "[AEFPaefp]"
        If test = False Then Cells(x, col).EntireRow.Delete
    Next
    End Sub

    BR
    Tommy Bak

  4. #4
    Thanks Tommy that was exactly what I needed.

  5. #5
    VBAX Regular
    Joined
    Jun 2004
    Location
    Denmark
    Posts
    58
    Location
    You're welcome, hondatiger.
    If you want to speed it up a bit insert this line in the top of each macro.

    Application.ScreenUpdating = False
    BR
    Tommy

  6. #6
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    Quote Originally Posted by tommy bak
    You're welcome, hondatiger.
    If you want to speed it up a bit insert this line in the top of each macro.

    Application.ScreenUpdating = False
    BR
    Tommy

    And at the end of the macro...

    Application.ScreenUpdating = True
    At least for Office 2002 and up, as it doesn't reset itself.

  7. #7
    VBAX Regular
    Joined
    Jun 2004
    Location
    Denmark
    Posts
    58
    Location
    I have heard about this before, Zack, but I have never experienced it myself.
    But you're right... one should do this as a precaution

    br
    Tommy Bak

  8. #8
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    Yeah, it doesn't make a huge difference, but when doing a lot of coding/using it does work better when you take that precaution. In my work add-in (that I pass around for everybody, common workplace functions) I have a procedure on mine that resets all application attributes. If I've been doing a lot of coding and/or testing, I usually run that routine to clean up after myself. It seems to help a little bit. I am messy though.. LOL

  9. #9
    Knowledge Base Approver VBAX Expert brettdj's Avatar
    Joined
    May 2004
    Location
    Melbourne
    Posts
    649
    Location
    If you can avoid looping it will save some time on the bigger datasets. This method uses AutoFilter on an inserted column to delete the dersired rows

    Cheers

    Dave

    Sub DelRowsII()
        Dim myRange As Range, myCol As String
        myCol = "A"
    Set myRange = Range(Cells(1, myCol), Cells(65536, myCol).End(xlUp))
    Application.ScreenUpdating = False
        'insert a calculation row
        myRange.Offset(0, 1).Columns.Insert
    With myRange.Offset(0, 1)
        .Formula = "=IF(OR(RC[-1]={""A"",""E"",""F"",""P""}),1,"""")"
        .Value = .Value
        'delete the rows with a 1 as they contain the matches
        .AutoFilter Field:=1, Criteria1:="1"
        .EntireRow.Delete
        .EntireColumn.Delete
        End With
    Application.ScreenUpdating = True
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •