CODEWITHSUNDEEP
excelvba
Leaum
Leaum

Reputation: 73

How to scan a single column for values then delete the row?

This macro I have written searches the entire page for the values; 0, 1, 2, or 3 and then deletes that row.

Sub delRows()
Dim cl     As Range
Dim uRng   As Range
Dim rng    As Range
Dim x      As Long

Set uRng = ActiveSheet.UsedRange
For Each cl In uRng
    If cl.Value = "0" Or cl.Value = "1" Or cl.Value = "2" Or cl.Value = "3" Then
        If x = 0 Then
            Set rng = cl
            x = 1
        Else: Set rng = Union(rng, cl)
        End If
    End If
Next cl
rng.EntireRow.Delete
End Sub

How do I change it to scan a single column for those values then delete the row?

I get errors.

Let's say I want to search in Column "J" in the worksheet?

Upvotes: 0

Views: 817

Answers (2)

Jean-François Corbett
Jean-François Corbett

Reputation: 38520

This just requires changing one line in your code:

Set uRng = ActiveSheet.Range("J:J")

To speed things up, you can specify a smaller range, e.g. Range("J1:J100") or wherever your data is. Else you will loop through the entire column, i.e. 65536 rows (Excel 2003) or a million rows (2007 and newer), which is very time consuming.

Upvotes: 1

eggplant_parm
eggplant_parm

Reputation: 242

Sub delRows2()
    Application.ScreenUpdating = False
    Dim r As Long, r1 As Long, r2 As Long, c As Long

    c = 10 '<--- this is the column you want to search
    r1 = ActiveSheet.UsedRange.Row
    r2 = r1 + ActiveSheet.UsedRange.Rows.Count - 1

    For r = r2 To r1 Step -1
        If Cells(r, c) = "0" or Cells(r, c) = "1" or Cells(r, c) = "2" or Cells(r, c) = "3" Then Cells(r, 1).EntireRow.Delete
    Next r
End Sub

Upvotes: 0

Related Questions