automate delete rows without data
 

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.

automate delete rows without data
 

"Amir" wrote:

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.

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.


--
John James
------------------------------------------------------------------------
John James's Profile:
http://www.excelforum.com/member.php...o&userid=32690
View this thread: http://www.excelforum.com/showthread...hreadid=532460

automate delete rows without data
 

On Aug 19, 4:21*pm, Amir wrote:
"Amir" wrote:
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.

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.

--
John James
------------------------------------------------------------------------
John James's Profile:http://www.excelforum.com/member.php...o&userid=32690
View this thread:http://www.excelforum.com/showthread...hreadid=532460

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.

automate delete rows without data
 

You could select a column and F5SpecialBlanksOK

EditDeleteEntire Row


Gord Dibben MS Excel MVP

On Tue, 18 Aug 2009 20:44:01 -0700, Amir
wrote:

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.