Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    1,023
    Thanks
    63
    Thanked 2 Times in 2 Posts

    Need macro to delete unwanted data

    I need all the rows from row 11 containing text "CURRENT CLOSING BALANCE" (Col C) + 2 rows above it to remain intact (i.e the first 10 rows to be ignored) I also need all the other rows to be deletedSee attached data. Your assistance is most appreciated
    Attached Files Attached Files
    Last edited by HowardC; 2011-05-09 at 10:21.

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,498
    Thanks
    212
    Thanked 852 Times in 784 Posts
    Howard,

    Give this a try:
    Code:
    Option Explicit
    
    Sub Macro1()
       Dim lRow As Long
       Dim lLastRow As Long
       
        ActiveCell.SpecialCells(xlLastCell).Select
        Selection.End(xlToLeft).Select
        
        lLastRow = ActiveCell.Row
        lRow = lLastRow
        
        Do
          If Trim(Cells(lRow, 3)) = "CURRENT CLOSING BALANCE" Then
            ' MsgBox "Range is: " & Range(Cells(lRow + 1, 1), Cells(lLastRow, 1)).Address
            Range(Cells(lRow + 1, 1), Cells(lLastRow, 1)).EntireRow.Delete
            lRow = lRow - 3  '*** Change to 4 if you want to keep the separating line! ***
            lLastRow = lRow
          End If
          lRow = lRow - 1
        Loop Until lLastRow < 13
        
    End Sub
    Note the comment about keeping the dividing line between each account.
    Also note the commented out MsgBox line...if you want to test the code w/o actually deleting anything just un-comment this line and comment out the line below
    Last edited by RetiredGeek; 2011-05-09 at 12:00.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  3. #3
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    1,023
    Thanks
    63
    Thanked 2 Times in 2 Posts

    Macro to delete Unwanted Data

    Thanks for the reply.

    My Apologies, my Spec has changed somewhat.


    1) Where the text "Cost" appears in Col C I need this to be retained up where the word "closing balance" appears 2 rows below the word "Cost" in Col C"

    2) the first eight Rows i.e row 1 to 8 for Eg A18

    3) all the other rows to be deleted

    Your assistance will be most appreciated

    Regards

    Howard

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,498
    Thanks
    212
    Thanked 852 Times in 784 Posts
    Howard,

    Now I'm confused?

    Could you take you sample workbook, copy Sheet1 to Sheet2 then delete all the rows you don't want on Sheet2 so we can see what the end product should look like?
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  5. #5
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    1,023
    Thanks
    63
    Thanked 2 Times in 2 Posts

    macro to delete Unwanted Data

    Hi RG

    Attached please find my spreadsheet. sheet2 shows what the data should look like after running the macro.

    If you need any further info, please let me know

    Regards

    Howard
    Attached Files Attached Files

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,939
    Thanks
    0
    Thanked 94 Times in 90 Posts
    Cross posted here, BTW.
    Regards,
    Rory
    Microsoft MVP - Excel.

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    Cross posted on this site as well at http://windowssecrets.com/forums/sho...to-retain-data

    Steve

Posting Permissions

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