Excel 2002 worksheet
How can I delete ALL empty rows at once? Can it be done?
|
Excel 2002 worksheet
Try this macro:
Sub Macro1() Dim j As Long Dim i As Long Dim r As Range j = 65536 For i = 1 To j If Application.CountA(Rows(i)) = 0 Then If r Is Nothing Then Set r = Rows(i) Else Set r = Union(r, Rows(i)) End If End If Next i If Not r Is Nothing Then r.Delete End If End Sub -- Gary''s Student "cmd661" wrote: How can I delete ALL empty rows at once? Can it be done? |
Excel 2002 worksheet
Hi
One way Rather than deleting, you could sort the data, and all empty rows would fall below your data. If existing sort order is important, then before sorting, use a spare column adjacent to your data to number the rows. Fill in 1 in row 1, 2 in row 2, mark both cells and double click the fill handle to fill down (the fill handle is the small black cross that appears if you hovver overe the bottom right of the pair of marked cells) Sort your data on a column other than this new column, and all the empty rows (other than the number you have inserted) will fall to the bottom. Highlight this block of rows at the bottom of your sheet and delete them. Then sort by the column with the numbers, to get the file back into original sort order. Delete added column if required. -- Regards Roger Govier cmd661 wrote: How can I delete ALL empty rows at once? Can it be done? |
All times are GMT +1. The time now is 11:47 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com