Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Thread: VBA mod

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

    Default VBA mod

    Hi all,

    can someone help me to mod this so that when the rows identified are moved to another sheet the subsequent blank row is deleted from the source table, please?

    Code:
    Sub Move_Closed()Dim Check 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("E" & r).Value = "Y" Then
                Rows(r).Cut Destination:=Worksheets("Completed").Range("A" & lastrow2 + 1)
                lastrow2 = lastrow2 + 1
                Else:
            End If
        Next r
    Application.ScreenUpdating = True
    End Sub

  2. #2
    Board Regular Logit's Avatar
    Join Date
    Aug 2016
    Location
    United States
    Posts
    2,783
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA mod

    .
    Code:
    Sub DelEmptyRows()
        Dim lr As Long
        Application.ScreenUpdating = False    '\/Change number 1 below to correspond to affected column.
        lr = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row '<----- Change to actual sheet name
        With Range("A1:A" & lr)                                   '<-- Change to corresponding row
            .Replace " ", "", xlWhole                               '<-- Currently checking for number 0. Edit as required
            .SpecialCells(4).EntireRow.Delete
        End With
        Application.ScreenUpdating = True
    End Sub

  3. #3
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,006
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default Re: VBA mod

    Maybe
    Code:
    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("E" & 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
                Else:
            End If
        Next r
        If Not Rng Is Nothing Then Rng.Delete
    Application.ScreenUpdating = True
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  4. #4
    Board Regular
    Join Date
    Aug 2010
    Posts
    180
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA mod

    this appears to work fantastically - thank you

  5. #5
    Board Regular Logit's Avatar
    Join Date
    Aug 2016
    Location
    United States
    Posts
    2,783
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA mod

    .
    You are welcome. Thank you for the mention.

  6. #6
    Board Regular
    Join Date
    Aug 2010
    Posts
    180
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA mod

    Quote Originally Posted by dantheram View Post
    this appears to work fantastically - thank you
    it appears i spoke to soon - the code does not function when there are multiple rows moved over, the blank rows are not deleted in these circumstances

    any ideas how to fix this?

    Dan

  7. #7
    Board Regular
    Join Date
    Aug 2010
    Posts
    180
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA mod

    I've isolated the problem to when non-consecutive rows are set to be deleted - when the rows follow each other in the table the macro works as desired

  8. #8
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,006
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default Re: VBA mod

    Based on your duplicate post I assume you are referring to the code in post#3.
    If that's the case it should work regardless of how many rows have Y in column E.
    Do the rows with Y in col E get copied to the "completed" sheet? If so have you changed the code in any way?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  9. #9
    Board Regular
    Join Date
    Aug 2010
    Posts
    180
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA mod

    Quote Originally Posted by Fluff View Post
    Based on your duplicate post I assume you are referring to the code in post#3.
    If that's the case it should work regardless of how many rows have Y in column E.
    Do the rows with Y in col E get copied to the "completed" sheet? If so have you changed the code in any way?
    Hi Fluff

    yep that's the code.

    they are copied over fine, it just if there are non consecutive "Y" entries the code fails - it still copies them over, it just doesn't delete the blanks

    "Delete method of Range class failed"
    Last edited by dantheram; Sep 9th, 2019 at 11:44 AM.

  10. #10
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,006
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default Re: VBA mod

    Is your data in a structured table?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

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
  •