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,891
    Thanks
    154
    Thanked 179 Times in 172 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,754
    Thanks
    129
    Thanked 693 Times in 629 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 08: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
    3,183
    Thanks
    152
    Thanked 589 Times in 561 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 14: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
  •