• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Delete Empty Rows

Hadi1973

New Member
Dear All,

This is my first time to use Macros, I need to create one to capture and delete all empty rows, since it is a huge database. can you help me out.
 

AlanSidman

Well-Known Member
Attach a sample file of 8-10 records showing what your data looks like so we can formulate a solution that works. There is nothing generic in VBA
 

Marc L

Excel Ninja
Hi, rather than deleting the empty rows you may just sort the database data range so without needing any VBA procedure …​
 

shili12

Member
Personally, i have the same issue but i use ASAP utilities to perform that task.
ASAP utilities download
 
Last edited by a moderator:

jolivanes

Member
There are different ways of doing things.
Here is another way.
Code:
Sub Or_So()
Dim lc As Long, lr As Long
lc = Cells.Find("*", , , , xlByColumns, xlPrevious).Column
lr = Cells.Find("*", , , , xlByRows, xlPrevious).Row
Application.ScreenUpdating = False
    With Cells(1, lc + 1).Resize(lr)
        .Formula = "=COUNTA(RC[" & -lc & "]:RC[-1])"
        .Value = .Value
        .Replace 0, "=XXX", xlWhole, , False, , False, False
        .SpecialCells(xlFormulas, xlErrors).EntireRow.Delete
    End With
Columns(lc + 1).ClearContents
Application.ScreenUpdating = True
End Sub
It would be nice to know how large your database is (Rows and Columns) and how long it takes to do the deed(s)
 
Top