none
excel formula to delete entire row RRS feed

  • Question

  • Hello

    I would like to be able to delete the entire row in excel 2016 if column A does not end with a "\

    then have it up the column up (clean up the white space)

    thank you



    • Edited by totalnet32 Friday, March 30, 2018 6:35 PM
    Friday, March 30, 2018 6:28 PM

Answers

  • A formula can not delete cells. It can effectively erase cells, but not actually remove them from the sheet. You would need to use a Macro for that.

    It would be a pretty simple Macro;

    Sub RemoveOffendingRows()
    
    Set lastWell = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell)
        For currentRow = lastWell.Row To 1 Step -1
          Set currentWell = ActiveSheet.Cells(currentRow, 1)
          If Right(currentWell.value, 1) <> "\" Then
            currentWell.EntireRow.Delete
            End If
        Next
    End Sub

    If you want to have each cell in a row be blank if the first column does not end in "\" you could use:

    =IF(RIGHT($A11,1)="\","Actual Value you desire","")

    Ethan


    Ethan Strauss

    Friday, March 30, 2018 8:07 PM

All replies

  • A formula can not delete cells. It can effectively erase cells, but not actually remove them from the sheet. You would need to use a Macro for that.

    It would be a pretty simple Macro;

    Sub RemoveOffendingRows()
    
    Set lastWell = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell)
        For currentRow = lastWell.Row To 1 Step -1
          Set currentWell = ActiveSheet.Cells(currentRow, 1)
          If Right(currentWell.value, 1) <> "\" Then
            currentWell.EntireRow.Delete
            End If
        Next
    End Sub

    If you want to have each cell in a row be blank if the first column does not end in "\" you could use:

    =IF(RIGHT($A11,1)="\","Actual Value you desire","")

    Ethan


    Ethan Strauss

    Friday, March 30, 2018 8:07 PM
  • Hi totalnet32,

    Agree with Ethan that it is not feasible to be realized using formula. Please refer to the macro provided by Ethan and see if it can be helpful. If yes, it would be appreciated that you can help mark it as answer to close the thread so that others who might have a similar question can more easily search for the solution. 

    Then here we mainly focus on general issues about Excel user interface, if you need further assistance regarding vba code, it is suggested to post a new thread to the following dedicated MSDN forum for Excel for better response:

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    Regards,

    Yuki Sun


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.

    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

    Monday, April 2, 2018 8:04 AM
    Moderator