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

[jeff]

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

[patel]

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

[jeff]

Thank you

[jeff]

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

[Admin]

Hi

Add the following line

on error resume next

before the line

.SpecialCells(xlCellTypeBlanks).EntireRow.Delete

[jeff]

Thanks Admin