CODEWITHSUNDEEP
excelvba
Finduilas
Finduilas

Reputation: 742

Deleting rows in a foreach

I'm trying to update a sheet with an import of a .CSV file.. I can read and update all the information. After the update I want to remove some data. All the rows with D empty must be deleted (whole row).

For that, I have a foreach that checks D3:D486 (is last row). After running the macro, there are some rows deleted, but not all the rows.

    Dim rCell As Range
    Dim rRng As Range
    Debug.Print CStr(LastRow)

    Set rRng = Worksheets("sheet1").Range("D3:D" + CStr(LastRow))

    For Each rCell In rRng.Cells
        If Not IsEmpty(rCell) Then
            Debug.Print rCell.Row
        Else
            Debug.Print "Empty"
            Worksheets("sheet1").Rows(rCell.Row).Delete
        End If
    Next rCell

I guess there is a problem with the for-each.. By example, If he delete row 100, the next time he goes to row 101.. But thats previous row 102.. I can save the cells maybe in an array, but then it would be the same. Except if I go the other way (from bottom to top). How can I solve this?

Upvotes: 2

Views: 394

Answers (3)

Spurious
Spurious

Reputation: 1995

Rex answer is correct, if you want to get cute you can also do it this way:

Sub DeleteRowsWithCriteria()
Dim rng As Range, rngCell As Range, rngDelete As Range
Set rng = Worksheets("sheet1").UsedRange.Columns("D:D").Cells 'Watch out here, if columns A-C are not all used, this doesn't work
For Each rngCell In rng
    If rngCell.Value = "" Then
        If rngDelete Is Nothing Then
            Set rngDelete = rngCell
        Else
            Set rngDelete = Union(rngDelete, rngCell)
        End If
    End If
Next rngCell
rngDelete.EntireRow.Delete xlShiftUp
End Sub

Upvotes: 1

Michael
Michael

Reputation: 71

I would do it like this:

Dim i As Integer
Dim rRng As Range
Debug.Print CStr(LastRow)

Set rRng = Worksheets("sheet1").Range("D3:D" + CStr(LastRow))

For i = 1 To rRng.Cells.Count
    If Not IsEmpty(Worksheets("Sheet1").Range("D:" + i).Value) Then
        Debug.Print rCell.Row
    Else
        Debug.Print "Empty"
        Worksheets("Sheet1").Range("D:" + i).EntireRow.Delete
        i = i - 1
    End If
Next

Upvotes: 2

Rex
Rex

Reputation: 2140

i think you've answered your own question: from bottom to top...

and you can try range.EntireRow.Delete method too, something like below

Dim rCell As Range
Dim lastRow, i
lastRow = 1000
For i = lastRow To 1 Step -1
    ' if condition met
    Worksheets("Sheet1").Range("D:" + i).EntireRow.Delete
Next

Upvotes: 7

Related Questions