Thread: Deleting complete row when content in a column

Is there anything else that's going to be anywhere in column F, or would it be safe to say:
If the cell at row x, column F is not blank, delete row x.

mew?

If so, run something like:

Dim x As Integer

For x = 1 To 10000
    If Cells(x, 6) <> vbNullString Then
        Rows(x).Delete Shift:=xlUp
        x = x - 1
    End If
Next x

mew!

That's a macro by the way.
Just lemme know if you need to know what to do with that.

wait...
that sounded a lot dirtier than it was supposed to...

Oh, don't be so hard on yourself.
We all started with =A1+B1

In your workbook:

Click on your windows button, the one you use to save a file.

On the bottom of that drop down window, click "Excel Options"

In the "Popular" tab, there should be an option that says "Show the developer tab in the ribbon". Check that box

Close the options screen

On the top of your window there will now be a tab called "Developer" on the far right. Click on that tab.

Click on the button that says "Macros"

Enter a name for your macro, call is something simple with no spaces, like: DeleteRows

Click on the button on the right of that window that says "Create"

Copy and paste what I've posted in that box there with all the gibberish language. Paste it in the row inbetween "Sub DeleteRows()" and "End Sub"

That should now look like this:

Sub DeleteRows()
Dim x As Integer

For x = 1 To 10000
    If Cells(x, 6) <> vbNullString Then
        Rows(x).Delete Shift:=xlUp
        x = x - 1
    End If
Next x
End Sub

Now on that same screen, above the white area you just pasted that into, there's a little green triangle that looks like a play button. When you mouse over it, it should say "Run Sub/Userform (F5)"

Press that button.

Zing.
Note that you should save your workbook under a new name, as this can not be undone. If you save over your original workbook, those email addresses that we just deleted are toast. If you don't need them EVER, then go ahead and save over it.

Hope that helps.

mew!

Mew...be sure to include "how to use a macro" instructions when you post macros in the non-programming forums. And the code should probably be all-inclusive...names and all.

======
How to use the macro:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save your sheet

The macro is installed and ready to use. Press Alt-F8 and select it from the macro list.

A non-macro way to do it with Excel's AutoFilter.

1) Make sure the top of the data has "titles" in it, if not, add titles.
2) Click on the title row (row1?) and select Data > Filter > AutoFilter to turn it on
3) Click on the dropbox for column F and select NON-BLANKS
4) Highlight all the rows you still see and delete them all (Edit > Delete
5) Turn off the Autofilter, same way you turned it on.

Now you have only rows that are blank in column F left.

Originally Posted by mewingkitty

Click on your windows button, the one you use to save a file.

On the bottom of that drop down window, click "Excel Options"

In the "Popular" tab, there should be an option that says "Show the developer tab in the ribbon". Check that box

I believe those are all Excel 2007 terms and features, not available in Excel 2000.

Alright so I'm an idiot.
This is not late breaking news. :P
sry!

Not at all. I forget to include those instruction in the non-programming forums all the time and have to go back and add them. Go team...

Glad to hear it.

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].


(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated)