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

[dunndealpr]

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?

[NBVC]

Try:

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)

[dunndealpr]

Try:

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.

[NBVC]

You said column H initially....

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

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.