Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Aug 2006
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA to delete data in last row of dynamic range

    Dear Excel Experts,

    I have a dynamic range of data starting in I6 to P6. Every time a new entry is made the new data is copied (Clicking a form button) from another part of the worksheet into the next row I7:P7, the next entry would go into I8:P8 and so on. If for some reason an entry error has been made I would like a macro that would clear the data in the last used row in columns I to P . Anybody out there could help with a VBA. Thank you for your assistance in advance.

    Best regards
    aussiehans

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Aussiehans,

    The following should work. I've added two commented out commands so you can select the type of deletion you want to perform, you didn't specify. If your range doesn't start in I1 just adjust the Range() value in the 1st statement.

    Code:
    Sub DelLastRowCols()
    
        Range("I1").End(xlDown).Select
        Range(Selection, Selection.End(xlToRight)).ClearContents                  '** Clear Contents    **
        'Range(Selection, Selection.End(xlToRight)).Selection.Delete Shift:=xlUp  '** Delete Cells Up   **
        'Range(Selection, Selection.End(xlToRight)).Selection.Entirerow.Delete    '** Delete Entire Row **
        
    End Sub
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    New Lounger
    Join Date
    Aug 2006
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Dear RetiredGeek,

    Thank you very much for your reply. I tweaked it to suit my application and it works a treat. I do appreciate the time you spend on people like me struggling with excel.

    All the best

    Aussiehans

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Aussiehans,

    You're welcome, glad to be of assistance.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

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