Delete rows based on column value

Question

I would like to know how to delete the rows based on column in VBA?

Here is my excel file

       A              B             C              D         E               F
     Fname          Lname         Email           city     Country     activeConnect
1     nikolaos       papagarigoui  np@rediff.com   athens   Greece         No
2     Alois          lobmeier      al@gmx.com      madrid   spain          No
3     sree           buddha        sb@gmx.com      Visakha  India          Yes

I want to delete the rows based on activeconnect(i.e "NO") those who don't have activeconnect "NO".

The output should be as below.

       A              B             C              D         E               F
      Fname          Lname         Email           city     Country     activeConnect
1     nikolaos       papagarigoui  np@rediff.com   athens   Greece         No
2     Alois          lobmeier      al@gmx.com      madrid   spain          No

firstly, the code has to select all the rows based on column header(activeconnect) status as "No",then it has to delete the rows

I have more of raw data that includes 15k rows and 26 columns. The code has to be work automatically when we execute in VBA.

the sheet name is "WX Messenger import" note: F1 is column header that is "activeConnect"

Here is my code.

Sub import()
lastrow = cells(rows.count,1).end(xlUp).Row

sheets("WX Messenger import").select
range("F1").select

End sub

after that im unable to do the code based on column header. could someone please let me know. The remaining code must select the rows based on activeConnect status as "NO" and then delete it.

Answer 1

Another version that is a bit more general than Matt's

Sub SpecialDelete()
    Dim i As Long
    For i = Cells(Rows.Count, 5).End(xlUp).Row To 2 Step -1
        If Cells(i, 5).Value2 = "No" Then
            Rows(i).Delete
        End If
    Next i
End Sub

Answer 2

This was the very first thing I learned how to do when I first started learning vba. I bought a book on it and saw that it was a direct example in the book (or at least it was similar). I would suggest you purchase a book or perhaps find an online tutorial. You'll be surprised at what you can accomplish. Consider this your first lesson, I guess. You can run this while this sheet is active and selected. I should warn you that normally posting questions without any evidence of trying to solve the problem yourself with, say, some code of your own, will likely get downvoted. Welcome to Stackoverflow, by the way.

'Give me the last row of data
finalRow = cells(65000, 1).end(xlup).row
'and loop from the first row to this last row, backwards, since you will
'be deleting rows and the loop will lose its spot otherwise
for i = finalRow to 2 step -1
    'if column E (5th column over) and row # i has "no" for phone number
    if cells(i, 5) = "No" then
        'delete the whole row
        cells(i, 1).entirerow.delete
    end if
'move to the next row
next i

Answer 3

A collection of standard VBA programming frameworks for performing this action would be incomplete without including at least one based upon the AutoFilter Method.

Option Explicit

Sub yes_phone()
    Dim iphn As Long, phn_col As String

    On Error GoTo bm_Safe_Exit
    appTGGL bTGGL:=False

    phn_col = "ColE(phoneno)##"

    With Worksheets("Sheet1")
        If .AutoFilterMode Then .AutoFilterMode = False
        With .Cells(1, 1).CurrentRegion
            iphn = Application.Match(phn_col, .Rows(1), 0)
            .AutoFilter field:=iphn, Criteria1:="<>yes"
            With .Resize(.Rows.Count - 1, .Columns.Count).Offset(1, 0)
                If CBool(Application.Subtotal(103, .Cells)) Then
                    .Delete
                End If
            End With
            .AutoFilter field:=iphn
        End With
        If .AutoFilterMode Then .AutoFilterMode = False
    End With

bm_Safe_Exit:
    appTGGL
End Sub

Sub appTGGL(Optional bTGGL As Boolean = True)
    Application.ScreenUpdating = bTGGL
    Application.EnableEvents = bTGGL
    Application.DisplayAlerts = bTGGL
End Sub

You will likely have to correct the phone column's header label. I took your sample verbatim. Bulk operations are generally faster than looping.

Before:

      

After:

      

Answer 4

Deleting a lot of rows is usually very slow.

This code is optimized for large data (based on delete rows optimization solution)

Option Explicit

Sub deleteRowsWithBlanks()
    Dim oldWs As Worksheet, newWs As Worksheet, rowHeights() As Long
    Dim wsName As String, rng As Range, filterCol As Long, ur As Range

    Set oldWs = ActiveSheet
    wsName = oldWs.Name
    Set rng = oldWs.UsedRange

    FastWB True
    If rng.Rows.Count > 1 Then
        Set newWs = Sheets.Add(After:=oldWs)
        With rng
            .AutoFilter Field:=5, Criteria1:="Yes"    'Filter column E
            .Copy
        End With
        With newWs.Cells
            .PasteSpecial xlPasteColumnWidths
            .PasteSpecial xlPasteAll
            .Cells(1, 1).Select
            .Cells(1, 1).Copy
        End With
        oldWs.Delete
        newWs.Name = wsName
    End If
    FastWB False
End Sub

---

Public Sub FastWB(Optional ByVal opt As Boolean = True)
    With Application
        .Calculation = IIf(opt, xlCalculationManual, xlCalculationAutomatic)
        .DisplayAlerts = Not opt
        .DisplayStatusBar = Not opt
        .EnableAnimations = Not opt
        .EnableEvents = Not opt
        .ScreenUpdating = Not opt
    End With
    FastWS , opt
End Sub

Public Sub FastWS(Optional ByVal ws As Worksheet = Nothing, _
                  Optional ByVal opt As Boolean = True)
    If ws Is Nothing Then
        For Each ws In Application.ActiveWorkbook.Sheets
            EnableWS ws, opt
        Next
    Else
        EnableWS ws, opt
    End If
End Sub
Private Sub EnableWS(ByVal ws As Worksheet, ByVal opt As Boolean)
    With ws
        .DisplayPageBreaks = False
        .EnableCalculation = Not opt
        .EnableFormatConditionsCalculation = Not opt
        .EnablePivotTable = Not opt
    End With
End Sub