VBA to Delete empty rows in excel

lorenzod · Sep 7th 2004

I have imported data to excel and i need to delete rows that are empty.
Can somebody help me pls....

Dreamboat · Sep 7th 2004

You need VBA?

Select a column.
Edit--Go to--Special and choose Blanks, Ok.
Edit--Delete; choose Entire Row.

lorenzod · Sep 7th 2004

This is too fast...!!!!
Thanks a lot Dreamboat... it works!!!!!

But i have another file that needs to check two columns if empty (i mean, column A is empty, but Column C is not and vice versa). I want to delete the rows if both Column A and C are empty. How can I do that?

Thank you in advance... I really do appreciate your help!

Dreamboat · Sep 7th 2004

We're working on creating code that'll do it for whatever column(s) are selected.

But try this for your columns A & C:

Code

Option Explicit 
Sub checkTwoCols() 
    Dim cel As Range, rng As Range 
    Set rng = Range("A2", Range("A65536").End(xlUp)) 
    For Each cel In rng 
        If cel.Value = "" Then 
            If cel.Offset(, 2).Value = "" Then 
                cel.EntireRow.Delete 
            End If 
        End If 
    Next cel 
    MsgBox "Task complete!", vbOKOnly + vbInformation, "Complete!" 
End Sub

Display More

I did not test, and I'll be going out for a few hours...

XL-Dennis · Sep 7th 2004

To to it manually use the Autofilter-function by:

1) Select the startcell for the list in the left upper corner.
2) Choose the command Data | Filter | Autofilter.
3) In column A select the option Empty from the drop down arrow.
4) Repeat step 3 for column C.

The only visible rows should now be only those that meet these two conditions.

[Edit]
Since the title of this message imply use of VBA pls check out the following post: http://ozgrid.com/forum/showthread.php?t=20029

Especially #4.

lorenzod · Sep 8th 2004

You guys are supercalifragilisticexpialidocious!!!!

I thing I have missed 2/3 of my professional carreer by not visiting this site before. I'm sure I have missed so many things out here. Is there a way to search for a specific topic (thread)? Is there a list of topics by category (let's say Filtering, Conditional Formatting, etc) where i can just simply click and view all the threads.

My sincerest thanks for your help!!!

More power to this site!!!!

Dreamboat · Sep 8th 2004

Hi, Lorenzo.

You can click here:
http://www.ozgrid.com/forum/search.php?

Or, on the dark-colored bar that goes across the top of the screen, click Search-->Advanced Search.

VBA to Delete empty rows in excel

Participate now!

Share