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?

[BobBridges]

There are ways to do that, but not with a worksheet function. I mention that because you used the word "function" in the subject line, and Excel worksheet functions only display data, never take any other action.

Still, there are things you can do. One obvious possibility is to sort on that column; all the rows without a value in that column will be sorted to the same place, and you can delete them all in a block.

If you don't like that option—for example if the sorting is important to you and you don't think you can put it back in the original order afterward—then you can write a VBA program, a "macro". I use VBA a lot, so it doesn't sound too hard for me, and if it's the sort of thing you're going to want to do more than just this once it's probably worth doing. But most people don't care to go to that effort, and if it's just the one time then maybe sorting it is the best idea. But if you want to try the macro option and have never done one before, let us know and someone (probably I) will coach you through it a little at a time.

[dunndealpr]

Hey Bob. Someone shot me this VBA script. Works beautiful.

Sub DeleteBlankRows()
Dim r As Long
For r = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
If Cells(r, 5) = "" Then Rows(r).Delete
Next r
End Sub