Results 1 to 4 of 4

Thread: a function that deletes all rows with no value in a certain column?

  1. #1

    a function that deletes all rows with no value in a certain column?



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hey all, I use Excel 2007. Can it be set up to delete all rows in an excel file when there is no value in a certain column? For instance, every row with no value in column H would automatically be deleted?







  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,437
    Articles
    0
    Excel Version
    Excel 2016
    Try:

    Code:
    Sub DeleteBlanks()
         Worksheets("Sheet1").Columns(8).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    End Sub

    where Sheet1 is name of sheet, .Columns(8) means column H (8th column)


  3. #3
    Quote Originally Posted by NBVC View Post
    Try:

    Code:
    Sub DeleteBlanks()
         Worksheets("Sheet1").Columns(8).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    End Sub

    where Sheet1 is name of sheet, .Columns(8) means column H (8th column)

    Thanks so much for your time.


    Columns A, B, C and D all have data. Column E is the column where if the cell is blank, I want the entire row deleted. How would the code be written in this case? And this is a VBA script, correct? Sorry, I'm kind of slow with Excel.


    Thank you again.

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,437
    Articles
    0
    Excel Version
    Excel 2016
    You said column H initially....

    Anyway you just need to change the 8 to 5 (for 5th column, column E).


    Code:
    Sub DeleteBlanks()
          Worksheets("Sheet1").Columns(5).SpecialCells(xlCellTypeBlanks).EntireRow.Delete 
    End Sub
    To use this, hit ALT+F11, then go to Insert|Module.

    Paste the above code in the VB editor.

    Now back in your sheet go to Developer tab, click Macros button, select the DeleteBlanks macro and click Run.


Posting Permissions

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