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