Deleting blank rows
Posted by A.R. on March 09, 2001 9:07 AM
I have blank rows in Excel that I need to delete. I need to have all the data all line up with no blank rows between them. Every 5th row is a blank row. Is there a way to delete all those blank rows all at once instead of deleting them manually one by one?
Posted by Ken on March 09, 2001 9:15 AM
Try this macro
Sub Delete_Empty_Rows()
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
Ken
Posted by Loren on March 09, 2001 9:47 AM
Non macro solution:
Insert a new column A; number it consecutively; Sort on column B.
The blank rows will float to the top or bottom; delete them.
Resort on Column A, which restores the original order.
Posted by Roger on March 09, 2001 10:04 AM
Here's another way to delete blank rows without a macro (and the URL for the site where I found it). When you highlight the range though, be sure that you pick a column that only has blanks in a completely blank row. If you choose another column that has an occasional blank cell (in an otherwise non-blank row), that row could be deleted inadvertently.
Remove Blank Rows
Highlight your range and go to Edit>Go to>Special and select "Blanks" then "Ok" now go to Edit>Delete or Ctrl+Shift+= (equal) and then select "Entire row" from the "Delete" dialog and click "Ok".
Posted by Mark W. on March 09, 2001 10:44 AM
Another non-macro approach
Apply an AutoFilter that displays those records
that have blanks in a "key" field and then delete
those records.
Posted by David Hawley on March 09, 2001 11:45 PM
Re: Another non-macro approach
If all Columns with Autofilter on happen to have a blank on the same row it wont work.
If you can afford to sort your data then Sorting would be the quickest.
DaveOzGrid Business Applications
Posted by Mark W. on March 10, 2001 11:27 AM
Re: Another non-macro approach
> If you can afford to sort your data then
> Sorting would be the quickest.
Yep! And if you don't won't to change the order
of the records by this process it would be advisable
to create a numbered series in an unused column
so that you can restore the original record order.
Posted by Dave Hawley on March 10, 2001 1:24 PM
Re: Another non-macro approach
If that's the case then it's probably not worth using the sort method but rather the SpecialCells>Blanks.
Dave
OzGrid Business Applications
