Deleting rows in Excel

Question

Is there anyway to have Excel 2007 delete rows with certain parameters? Here is what I am trying to do:

I have an auto generated Excel .cvs file, which another program generates. But I need certain rows deleted and there are a lot of them scattered throughout the file. What I would like to be able to do is if one column is empty (i.e. no data in that column), I would like Excel to completely delete that whole row...all data in that row, in all the columns, all the way across, for that particular row. Right now, I am doing this manually and it takes forever.

Suggestions appreciated!

Answer 1

You could use a macro stored in an Excel workbook. . . .you can't store macros in a CSV file.

You would have both files open with CSV file active then run the macro from the VB Editor or a button on the QAT

Will delete all rows in the CSV file in the selected column.

Public Sub DeleteRowOnCell()

'delete any row that has a blank in selected column(s)

'select column header, not just a cell

   Set coltocheck = Application.InputBox(prompt:= _

            "Select A Column", Type:=8)

        On Error Resume Next

    coltocheck.SpecialCells(xlCellTypeBlanks).entirerow.Delete

End Sub

Gord

Answer 2

If there is a hard way to do something, I'll find it<g>

Forget the other post.

How about selecting the desired column then F5>Special>Blanks>OK

Edit>Delete>Entire Row.

Gord

Answer 3

If there is a hard way to do something, I'll find it<g>

Forget the other post.

How about selecting the desired column then F5>Special>Blanks>OK

Edit>Delete>Entire Row.

Gord

OK, tried that. Couldn't find the "Edit" menu so did it from the right clicking and then selecting delete "entire row". That only deletes one row at a time...correct? Or at least it did for me. I was already deleting one at a time. Was looking for a way to to "all" of the rows that have that one cell blank, in one shot.

Answer 4

Sorry about that.  Instructions from Excel 2003.

In 2007, after you F5>Special>Blanks>OK, click on dropdown arrow of Delete and choose Delete Sheet Rows.

Gord

Answer 5

Thanks for the suggestion. I finally figured that part out...LOL! But, that only deletes a single row at a time...correct? Which is what I was already doing. I need a way to "mass" delete all those rows with blanks. Do that make sense?