CODEWITHSUNDEEP
excelvbaspreadsheet
Rezkin
Rezkin

Reputation: 31

How to I delete rows in Excel if a cell/column does not contain text?

I have a spreadsheet with data in tables. In some of the rows there is a specific text in the same column. how can i delete all the rows expect those that have that text?

Upvotes: 0

Views: 590

Answers (1)

KingsInnerSoul
KingsInnerSoul

Reputation: 1382

If I understand you right, the following code deletes rows that do not contain TEXT in column 2 of your active spreadsheet.

Sub deleteit()
    Dim colNo As Long:      colNo = 2             ' hardcoded to look in col 2
    Dim ws    As Worksheet: Set ws = ActiveSheet  ' on the active sheet
    Dim rgCol As Range
    Set rgCol = ws.Columns(colNo)                          ' full col range (huge)
    Set rgCol = Application.Intersect(ws.UsedRange, rgCol) ' shrink to nec size
    Dim rgZeroCells As Range ' range to hold all the "0" cells (union of disjoint cells)
    Dim rgCell      As Range ' single cell to iterate
    For Each rgCell In rgCol.Cells
        If Not IsError(rgCell) Then
            If rgCell.Value <> "TEXT" Then                 ' Your TEXT string goes here
                If rgZeroCells Is Nothing Then
                    Set rgZeroCells = rgCell ' found 1st one, assign
                Else
                    Set rgZeroCells = Union(rgZeroCells, rgCell) ' found another, append
                End If
            End If
        End If
    Next rgCell
    If Not rgZeroCells Is Nothing Then
        rgZeroCells.EntireRow.Delete ' deletes all the target rows at once
    End If
End Sub

Hope it helps

Upvotes: 1

Related Questions