IsEmpty functionality issue

Question

can anyone please advise me on what I am doing wrong with this procedure? It should delete about 6 rows in my file, but when I run it, no effect. In about 6 rows, there is no data in columns B and C, their position is dynamic and I want to get rid of those rows.

Thank you

Dim lastrow As Integer

lastrow = Application.WorksheetFunction.CountA(ThisWorkbook.Sheets("ISSUES").Range("D1:D5000"))

For i = 1 To lastrow
    If IsEmpty(ThisWorkbook.Sheets("ISSUES").Cells(i, 2)) = True And IsEmpty(ThisWorkbook.Sheets("ISSUES").Cells(i, 3)) = True Then
        ThisWorkbook.Sheets("ISSUES").Cells(i, 2).EntireRow.Delete
    End If
Next i

Then make sure lastrow is greater than zero, and that the "no data in columns B and C" cells are actually empty, as opposed to e.g. containing a formula, a blank string or being formatted to visually hide the value. — GSerg
– GSerg, Commented Aug 26, 2016 at 19:25
Also, remove the =True portion for your If statements. They aren't needed. — Ryan Wildry
– Ryan Wildry, Commented Aug 26, 2016 at 19:26
@GSerg - The point is that it has nothing to do with the cell - it is a VARTYPE test for the return value of ThisWorkbook.Sheets("ISSUES").Cells(i, 2). It's almost always the wrong test in this context, as evidenced by the OP's comment above. Test the business rule - not the variable type. — Comintern
– Comintern, Commented Aug 26, 2016 at 21:57

cyboashu · Accepted Answer · 2016-08-26 19:29:27Z

The second issue will be performance. Deleting one row at a time will make your macro very slow for large set of rows, like you are looking for 5000 rows here.

Best way is to club them together and then delete in one go. Also helps to avoid reverse loop.

Sub test()


Dim lastrow          As Long
Dim rngDelete        As Range

lastrow = Application.WorksheetFunction.CountA(ThisWorkbook.Sheets("ISSUES").Range("D1:D5000"))

For i = 1 To lastrow
    If IsEmpty(ThisWorkbook.Sheets("ISSUES").Cells(i, 2)) = True And IsEmpty(ThisWorkbook.Sheets("ISSUES").Cells(i, 3)) = True Then
        'ThisWorkbook.Sheets("ISSUES").Cells(i, 2).EntireRow.Delete

        '/Instead of deleting one row at a time, club them in a range. Also no need for reverse loop
        If rngDelete Is Nothing Then
            Set rngDelete = ThisWorkbook.Sheets("ISSUES").Cells(i, 2)
        Else
            Set rngDelete = Union(rngDelete, ThisWorkbook.Sheets("ISSUES").Cells(i, 2))
        End If


    End If
Next i


    '/ Now delete them in one go.
    If Not rngDelete Is Nothing Then
        rngDelete.EntireRow.Delete
    End If

End Sub

Totos · Accepted Answer · 2016-08-26 19:36:27Z

0

Another solution:

Dim lastrow As Integer
Dim cond1, cond2 As Range

lastrow = Application.WorksheetFunction.CountA(ThisWorkbook.Sheets("ISSUES").Range("D1:D5000"))

For i = lastrow To 1 Step -1
    Set cond1 = ThisWorkbook.Sheets("ISSUES").Cells(i, 2)
    Set cond2 = ThisWorkbook.Sheets("ISSUES").Cells(i, 3)

    If cond1.Value = "" Then
        cond1.ClearContents
    End If
    If cond2.Value = "" Then
        cond2.ClearContents
    End If

    If IsEmpty(cond1.Value) = True And IsEmpty(cond2.Value) = True Then
        ThisWorkbook.Sheets("ISSUES").Cells(i, 2).EntireRow.Delete
    End If
Next i

answered Aug 26, 2016 at 19:36

Totos

311 silver badge5 bronze badges

Collectives™ on Stack Overflow

IsEmpty functionality issue

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related