Results 1 to 6 of 6

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

  1. #1
    Senior Member
    Join Date
    Jul 2013
    Posts
    102
    Rep Power
    12

    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
    Attached Files Attached Files

  2. #2
    Member
    Join Date
    Jun 2013
    Posts
    93
    Rep Power
    12
    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

  3. #3
    Senior Member
    Join Date
    Jul 2013
    Posts
    102
    Rep Power
    12
    Thank you

  4. #4
    Senior Member
    Join Date
    Jul 2013
    Posts
    102
    Rep Power
    12
    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
    Attached Files Attached Files

  5. #5
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi

    Add the following line
    Code:
    on error resume next
    before the line

    Code:
    .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  6. #6
    Senior Member
    Join Date
    Jul 2013
    Posts
    102
    Rep Power
    12
    Thanks Admin

Similar Threads

  1. Find All Empty Blank Cells Or KeyWord In A Column
    By william516 in forum Excel Help
    Replies: 9
    Last Post: 06-25-2013, 05:20 PM
  2. Replies: 13
    Last Post: 06-10-2013, 09:05 AM
  3. Delete Remove Rows By Criteria VBA Excel
    By marreco in forum Excel Help
    Replies: 5
    Last Post: 12-20-2012, 05:56 PM
  4. Replies: 2
    Last Post: 09-24-2012, 11:19 PM
  5. Delete Empty Rows
    By Rasm in forum Excel Help
    Replies: 4
    Last Post: 04-28-2011, 02:13 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •