Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    May 2015
    Posts
    13
    Thanks
    5
    Thanked 0 Times in 0 Posts

    clear contents of a range beginning from last row with null value at a cell in a given range

    Hello guys,
    I needed this as a part of my excel table which i intend to let delete the given range in the last row.
    It works pretty good. I need to add another control here -with below code- i would like to check if the cell at 'K' or the cell at 'M' column of the target range is not null.
    If either of these cells are not null, then i'd like to exit, otherwise i'd like to go on and clear the contents. Below is how i use it right now. I'd appreciate if you can help me out and let me learn about it. Thanks.


    Private Sub CommandButton22_Click()
    Range("A1:N1").End(xlDown).Select
    Range(Selection, Selection.End(xlToRight)).ClearContents
    Range("A1").Select
    End Sub

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

    Welcome to the Lounge as a New Poster!

    Code:
    Option Explicit
    
    Private Sub TestClearData()
    
      Dim lLastRow  As Long
      
      lLastRow = Range("A1").End(xlDown).Row()
      If Cells(lLastRow, 11).Value = 0 And _
         Cells(lLastRow, 13).Value = 0 Then
         
        Range(Cells(lLastRow, 1), Cells(lLastRow, 14)).ClearContents
        
      End If
      
      Range("A1").Select
      
    End Sub
    I made major changes to your code to make it both more efficient (not doing selects) and more accurate as your code would not clear column N. Of course if you don't want column N cleared just change the 14 to a 13.

    BTW: You can just paste the code into your button sub.

    Test File: lerq.xlsm


    HTH
    Last edited by RetiredGeek; 2015-05-18 at 11:42.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. The Following User Says Thank You to RetiredGeek For This Useful Post:

    lerq (2015-05-18)

  4. #3
    New Lounger
    Join Date
    May 2015
    Posts
    13
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Hi,
    Thanks very much for your help. It works just the way i need it.

Posting Permissions

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