PDA

View Full Version : a function that deletes all rows with no value in a certain column?



dunndealpr
2013-06-06, 09:45 PM
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
2013-06-06, 10:03 PM
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
2013-06-06, 10:13 PM
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
2013-06-06, 10:19 PM
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.