Results 1 to 2 of 2

Thread: Move row then delete blank
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Aug 2010
    Post Thanks / Like
    0 Post(s)
    0 Thread(s)

    Default Move row then delete blank

    Hi all,

    could someone help me mod this to delete each blank row after it has moved the data to another sheet. Currently, it works when there is only one "Y" marked row but, if there are multiple rows, it does not delete any of the blank rows.

    Sub Move_Closed()Dim Check As Range, rng As Range, r As Long, lastrow2 As Long, lastrow As Long
    Application.ScreenUpdating = False
    lastrow = Worksheets("Open").UsedRange.Rows.Count
    lastrow2 = Worksheets("Completed").UsedRange.Rows.Count
    If lastrow2 = 1 Then lastrow2 = 0
        For r = lastrow To 2 Step -1
            If Range("F" & r).Value = "Y" Then
                Rows(r).Cut Destination:=Worksheets("Completed").Range("A" & lastrow2 + 1)
                If rng Is Nothing Then Set rng = Rows(r) Else Set rng = Union(rng, Rows(r))
                lastrow2 = lastrow2 + 1
            End If
        Next r
        If Not rng Is Nothing Then rng.Delete
    Application.ScreenUpdating = True
    End Sub
    thanks in advance
    0 0

  2. #2
    MrExcel MVP
    Fluff's Avatar
    Join Date
    Jun 2014
    Post Thanks / Like
    470 Post(s)
    47 Thread(s)

    Default Re: Move row then delete blank


    Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread.
    Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10
    0 0

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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