Skipping single line of code in loop: entirerow.delete in another workbook

Question

Fairly new to VBA, but I am trying to import (copy/paste) data from a selected workbook into my master workbook(holds code), but first need to delete unusable rows in the selected workbook.

If Column C is empty, then I want to delete the entirerow, using a bottom to the top loop, before I copy the data into my master workbook (hence the need to more explicitly refer to each wb).

It's currently skipping "wb.Sheets(1).Rows(r).EntireRow.Delete" line of code within my loop, but executing the loop the correct amount of times. Please help , still learning.

Earlier, it was incorrectly deleting every row: when my if then clause referred to a a different cell (one with a value, not a blank, in it).

Dim wb As Workbook
Dim r As Integer

With wb.Sheets(1)       
     For r = wb.Sheets(1).UsedRange.Rows.Count To 1 Step -1
         If wb.Sheets(1).Cells(r, "C") = "0" Then
             wb.Sheets(1).Rows(r).EntireRow.Delete 
         End If
     Next

Possible duplicate of [Excel VBA - Delete empty rows](https://stackoverflow.com/questions/9379673/excel-vba-delete-empty-rows) — BigBen, Jan 18 '19 at 13:19

Tom · Accepted Answer · 2019-01-18T13:28:41.150

0

Something like this should work. You've used a With block for the code but then not actually used it. You don't need the additional references to the same object. In this I've tested for whether the cell is empty of not using IsEmpty

Dim wb As Workbook
Dim r As Integer

With wb.Sheets(1)
    For r = .UsedRange.Rows.Count To 1 Step -1
        If IsEmpty(.Cells(r, 3)) Then
            .Rows(r).Delete
        End If
    Next
End With

edited Jan 18 '19 at 13:28

answered Jan 18 '19 at 13:19

Tom

9,725
3
31
48

That works great! Thank you for your help, and showing me something new. And yes, i didn't need to include the "with" here, I do use it later in the code but didn't realize how that would be confusing in this context. – Baksi Jan 18 '19 at 13:27
No problem - was a good start just needed some finessing. If this is now solved don't forget to mark it with the tick on the left hand side of the answer – Tom Jan 18 '19 at 13:28
1

Also glad you told me that. You're awesome Tom – Baksi Jan 18 '19 at 13:30

score 0 · Answer 2 · answered Jan 18 '19 at 13:44

Delete Rows (Criteria Column)

Union Version

Sub DelRows()

    Const cSheet As Variant = 1     ' Worksheet Name/Index
    Const cFirst As Long = 1        ' First Row Number
    Const cColumn As Variant = "C"  ' Criteria Column Letter/Number

    Dim rngU As Range   ' Union Range
    Dim LastUR As Long  ' Last Used Row
    Dim r As Long       ' Row Counter

    With ThisWorkbook.Worksheets(cSheet)

        ' Check if worksheet is empty.
        If .Cells.Find("*", .Cells(.Rows.Count, .Columns.Count), -4123, , 1) _
                Is Nothing Then Exit Sub
        ' Calculate Last Used Row.
        LastUR = .Cells.Find("*", , , , , 2).Row

        ' Add found criteria first (can be any) column cells to Union Range.
        For r = cFirst To LastUR ' Rows
             If IsEmpty(.Cells(r, cColumn)) Then
                 If Not rngU Is Nothing Then
                     Set rngU = Union(rngU, .Cells(r, 1))
                   Else
                     Set rngU = .Cells(r, 1)
                 End If
             End If
        Next

    End With

    ' Delete rows in one go.
    If Not rngU Is Nothing Then
        rngU.EntireRow.Delete ' .Hidden = True
        Set rngU = Nothing
    End If

End Sub

Skipping single line of code in loop: entirerow.delete in another workbook

2 Answers2

Delete Rows (Criteria Column)

Union Version