Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Oct 2003
    Posts
    82
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel macro to remove rows

    I need a macro that will find the All rows that BEGIN with the following text "Monthly Summary ***" and then delete all the rows in BETWEEN a row that begins with the text "Agent ****". I do not want to delete the Rows that begin with the text Agent.

    Here's a brief description: find monthly summary, then find Agent, then delete all rows including the monthly summary row but not the Agent row, find the next monthly summary row and repeat delete, find next monthly row and repeat delete.

  2. #2
    Star Lounger
    Join Date
    Oct 2003
    Posts
    82
    Thanks
    0
    Thanked 0 Times in 0 Posts
    anyone?

  3. #3
    WS Lounge VIP access-mdb's Avatar
    Join Date
    Dec 2009
    Location
    Oxfordshire, UK
    Posts
    1,721
    Thanks
    146
    Thanked 156 Times in 149 Posts
    Patience friend - 25 minutes is too short a time to expect a reply. No doubt people are writing code as I type!

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    Aluislugo,

    This code should do what you are asking assuming that Monthly Summary and Agent reside in column A

    Code:
    Public Sub RemoveRows()
    LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    NextCycle:
    For I = LastRow To 2 Step -1
        If Cells(I, 1) = "Agent ****" Then
            StopRow = I
            Exit For
        End If
    Next I
    For I = LastRow To 2 Step -1
        If Cells(I, 1) = "Monthly Summary ***" Then
            StartRow = I
            Exit For
        End If
    Next I
    If StopRow = 0 Then Exit Sub
    Range(Cells(StartRow, 1), Cells(StopRow - 1, 1)).EntireRow.Delete
    LastRow = StartRow - 1:: StartRow = 0:: StopRow = 0
    GoTo NextCycle
    End Sub
    HTH,
    Maud

  5. #5
    Star Lounger
    Join Date
    Oct 2003
    Posts
    82
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hello thank you for trying to help. However, when I ran the macro it did not do anything. Attached is a small sample. The red highlighted cells (which may vary) represents what I would like the macro to delete. tks.
    Attached Files Attached Files
    Last edited by aluislugo; 2015-08-20 at 09:35.

  6. #6
    Star Lounger
    Join Date
    Oct 2003
    Posts
    82
    Thanks
    0
    Thanked 0 Times in 0 Posts
    any help please?

  7. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi

    ..it would have been easier if you posted the sample file first.
    ..Then Maud would know your column [A] is empty.
    ..and there isn't an 'Agent' row after the very last block to be deleted.
    ..and he would have put the code in a standard vba module, NOT the workbook module.

    Anyway, the attached modified code with your sample data works OK.

    zeddy
    •Excel Tactical Surveillance Support
    .
    Attached Files Attached Files
    Last edited by zeddy; 2015-08-20 at 15:31.

  8. #8
    Star Lounger
    Join Date
    Oct 2003
    Posts
    82
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Sorry Zeddy my bad. I wish I had done this too b/c I spent the whole day cleaning this up manually and I only have one more file to do. Thank you never the less.


    Quote Originally Posted by zeddy View Post
    Hi

    ..it would have been easier if you posted the sample file first.
    ..Then Maud would know your column [A] is empty.
    ..and there isn't an 'Agent' row after the very last block to be deleted.
    ..and he would have put the code in a standard vba module, NOT the workbook module.

    Anyway, the attached modified code with your sample data works OK.

    zeddy
    •Excel Tactical Surveillance Support
    .

Posting Permissions

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