Results 1 to 3 of 3
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,422
    Thanks
    125
    Thanked 5 Times in 5 Posts

    Macro to delete unwanted Rows

    I would like a macro to delete all rows , except where there text starts with “NEW & USED INVENTORY CONSOLIDATED” in Col A up to where the first “Total” appears below this . In this example row 99 to 126 must NOT be deleted, the rest of the rows to be deleted (the range size between text “NEW & USED INVENTORY CONSOLIDATED” up to where the first “Total” appears below this increases or decreases each month


    It would be appreciated if someone can please assist me in resolving this
    Attached Files Attached Files

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Howard,

    This will delete the rows above and below the section you want to keep.

    HTH,
    Maud

    Code:
    Public Sub DelRows()
    Dim rng As Range
    With ActiveSheet
    Lastrow = .Cells(Rows.Count, 1).End(xlUp).Row
    Set rng = .Columns("A:A").Find(What:="NEW & USED INVENTORY CONSOLIDATED", After:=.Range("A1"), _
                    LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
                    SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    .Range("A1:" & rng.Offset(-1, 0).Address).EntireRow.Delete
    Set rng = .Columns("A:A").Find(What:="TOTAL", After:=.Range("A1"), _
                    LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
                    SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    .Range(rng.Offset(1, 0).Address & ":A" & Lastrow).EntireRow.Delete
    End With
    End Sub

  3. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,422
    Thanks
    125
    Thanked 5 Times in 5 Posts
    Hi Maud

    Thanks very much for the help

Posting Permissions

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