automate delete rows without data

  • Thread starter Thread starter Amir
  • Start date Start date
A

Amir

It is tediious to manually delete rows that are empty and I was wondering if
there was a way to automatically delete empty rows either by using a function
or creating a macro or if there was any other easier solution.
Thank you.
 
Amir said:
It is tediious to manually delete rows that are empty and I was wondering if
there was a way to automatically delete empty rows either by using a function
or creating a macro or if there was any other easier solution.
Thank you.
Click to expand...

Found something from John James...
Works great...Thanks John...

If you want to delete blank rows on your datasheet, here's a routine
which does this:

Sub DeleteEmptyRows()
LastRow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For r = LastRow To 1 Step -1
If Application.CountA(Rows(r)) = 0 Then Rows(r).Delete
Next r
End Sub

If your formulae are slowing down your worksheet, then maybe try
getting the same result but without formulae. For instance if you
filter your data on your datasheet, then you'll be able to copy the
filtered range onto another sheet. If this meets your needs, you could
look at attaching this filter & paste process to a macro/button.
 
Found something from John James...
Works great...Thanks John...

If you want to delete blank rows on your datasheet, here's a routine
which does this:

Sub DeleteEmptyRows()
LastRow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For r = LastRow To 1 Step -1
If Application.CountA(Rows(r)) = 0 Then Rows(r).Delete
Next r
End Sub

If your formulae are slowing down your worksheet, then maybe try
getting the same result but without formulae.  For instance if you
filter your data on your datasheet, then you'll be able to copy the
filtered range onto another sheet.  If this meets your needs, you could
look at attaching this filter & paste process to a macro/button.
Click to expand...

For a non macro way, you can put in a new index column and put 1 and 2
in the top two cells. Fill down to the bottom of the data. Sort
everything on another column. All the blanks go together at the
bottom. Delete them all at once. Sort on the index column. and
delete it.
 
You could select a column and F5>Special>Blanks>OK

Edit>Delete>Entire Row


Gord Dibben MS Excel MVP
 
Back
Top