Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel 2002 worksheet
How can I delete ALL empty rows at once? Can it be done?
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cannot use Data Analysis tools on Excel worksheet embedded in PwrP | Excel Worksheet Functions | |||
Excel 2002; copy formulas to one workbook to another diff data | Excel Worksheet Functions | |||
Excel 2003, Convert EXISTING Worksheet Data to XML? | Excel Discussion (Misc queries) | |||
Excel worksheet linking | Excel Worksheet Functions | |||
Excel 2002 calcs slower than Excel 97 | Excel Discussion (Misc queries) |