How To Remove or Delete Blank Or Empty Rows After Data Is Transferred

Hi

This code is very good but can it remove the empty rows from the task sheet once the data has been transferred to the Archive sheet.This way there is no gaps between tasks in the Task sheet

Best Regards

Jeff

Code:

---

Sub ArchiveCompleted()
    Dim rng As Range
    With Worksheets("TASKS")
        For Each rng In .Range("E5:E" & Application.Max(.Cells(.Rows.Count, "E").End(xlUp).Row, 5))
            If LCase(rng.Value) = "completed" Then
                With Worksheets("ARCHIVE")
                    .Cells(.Rows.Count, "B").End(xlUp)(2).Resize(, 2).Value = Array(rng.Offset(, -3).Value, rng(1, 2).Value)
                    rng.Offset(, -3).Resize(, 5).ClearContents
                End With
            End If
        Next rng
        With .Range("B5:B" & Application.Max(.Cells(.Rows.Count, "B").End(xlUp).Row, 5))
          .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
        End With
    End With
End Sub

---

Thank you

The code transfers tasks completed to Archive sheet but it also comes up with a Error - Runtime Error 1004 No Cells Were Found
This line of code -
.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Any thoughts
Thanks
Jeff

Hi

Add the following line

Code:

---

on error resume next

---

before the line

Code:

---

.SpecialCells(xlCellTypeBlanks).EntireRow.Delete

---

Thanks Admin