Excel VBA Range Finding and Deletion

Question

I have a couple of questions regarding VBA which I hope you folks can help me with. I'm a very new coder to VBA, so any help you can provide is very much appreciated.

Objective - Remove all rows from "cellRange" if a similar value is found in "valueRange"

Code so far

Sub DeleteRows()

Set valueRange = Worksheets("Delete Rows").Range("A4:A65000")
Set cellRange = Worksheets("Load File").Columns(Worksheets("Delete Rows").Range("F1").Value)

For Each Cel In cellRange.Cells
    For Each Value In valueRange.Cells
        If Cel.Value = Value.Value Then
            Cel.EntireRow.Delete
        End If
    Next Value
Next Cel
End Sub

Problem 1: valueRange doesn't always have all 65000 rows populated. How can I only make it so that the range only grabs those from A4:(until it hits an empty column)

Problem 2: Similar to problem 1, but the cellRange

Problem 3: Whenever a row is deleted, it seems to affect how the range is set. Meaning that if it deletes row #10 in, then the loop goes to row#11 without checking row #10 again. How can I tell the look to do a second pass or to go through the file again.

few quick tips... 1 & 2. use something like this to find range you need (continuous range): `Set valueRange = Worksheets("Delete Rows").Range("A4", Worksheets("Delete Rows").Range("A4").End(xlDown))`... 3. please refer to [this answer](http://stackoverflow.com/a/15511293/2143262) — Kazimierz Jawor, Apr 19 '13 at 13:41
and one additional tip: to find last row from which you will start your loop use that kind of logic `lastRow = cellrange.Row + cellrange.Rows.Count - 1` — Kazimierz Jawor, Apr 19 '13 at 13:51

score 2 · Accepted Answer · edited May 23 '17 at 10:33

P1: Two options here
a) if the Cel.Value is Empty, Exit For
b) proper range selection, refer to this guy here: Excel: Selecting all rows until empty cell

P2: Same as above

P3: As For-Each can't go "backwards" the best you can do is
a) Don't delete the row but store it's number instead e.g. in a Long array, then add a For-Next and delete the "marked" rows like:

For x = UBound(myLongArray)-1 To 0 Step -1
    cel(x).EntireRow.Delete
Next x

b) instead of For-Each, store the number of rows (via the ROWS function) in a variable and go through the rows with a 'Step -1' loop

score 1 · Answer 2 · answered Apr 19 '13 at 13:57

As others mention, you have to step backwards when deleting.

Also, I modified to avoid unnecessary iteration over each cell in ValueRange, instead use the Match() function to check if Cel.Value exists in ValueRange.

Sub DeleteRows()
Dim r as Long
Dim valueRange as Range, cellRange as Range
Dim Cel as Range

Set valueRange = Worksheets("Delete Rows").Range("A4:A65000").End(xlUp) '<~~ Get the last unused row
Set cellRange = Worksheets("Load File").Columns(Worksheets("Delete Rows").Range("F1").Value)

For r = cellRange.Cells.Count to 1 Step -1  '<~~ When deleting rows you must step backwards through the range to avoid the error you are encountering.'
    Set Cel = cellRange.Cells(r)

    'Check to see if Cel.Value exists in the ValueRange using the "Match" function'
    If Not IsError(Application.Match(Cel.Value,ValueRange,False) Then
        Cel.EntireRow.Delete
    End If
Next r
End Sub

Robert Co · Answer 3 · 2013-04-19T21:53:53.803

Here you go.

' Declare your variables to get intellisense
Dim rngDelete As Range
Dim cellRange As Range
Dim valueRange As Range

' Get only the rows with data
Set valueRange = Worksheets("Delete Rows").Range("A4")
If valueRange.Offset(1, 0) <> "" Then
    Set valueRange = Worksheets("Delete Rows").Range(valueRange, valueRange.End(xlDown))
End If

' Get only the rows with data
Set cellRange = Worksheets("Load File").Cells(Worksheets("Delete Rows").Range("F1").value,1)
If cellRange.Offset(1, 0) <> "" Then
    Set cellRange = Worksheets("Load File").Range(cellRange, cellRange.End(xlDown))
End If

Dim cel As Range
Dim value As Range

' make cel your outer since it has more rows
For Each cel In cellRange.Cells
    For Each value In valueRange.Cells
        If value.value = cel.value Then
            ' Don't delete it yet but store it in a list
            If rngDelete Is Nothing Then
                Set rngDelete = cel.EntireRow
            Else
                Set rngDelete = Union(rngDelete, cel.EntireRow)
            End If
            ' no need to look further
            Exit For
        End If
    Next
Next

' Wipe them out all at once
rngDelete.Delete

Excel VBA Range Finding and Deletion

3 Answers3