Excel - How to
delete blank lines another method
This is actually the method I use. It is much like the first method but instead of deleting the lines one at a time it puts a hash mark, pound sign, into the cell in the A column. The when you sort on the A column all the "#" will be grouped together and you can delete them all at once. This is much faster than the first method.
Then you can sort on Column A and deleted the rows with the hash marks "#." Be sure you have a routine to return to the original order
'After this is run all blank rows will contain a "#"
'Put the cursor in Cell A1 before you start this routine
'This line turns the screen refresh off so it speeds up the process
Application.ScreenUpdating = False
'I like to save the current cell address, in "C" so I can return to it
C = ActiveCell.Address
'This code determines the last row that contains data and places it in "D"
D = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
'Keep going untile no more data
While ActiveCell.Row <= D
' Uses COUNTA to see if there is data in the row
If WorksheetFunction.CountA(Selection.EntireRow) = 0 Then
' If the row is empty place a "#" in the cell
Selection = "#"
' Go to the next row
'Return to the original cell address
'Turn screen refresh back on
Application.ScreenUpdating = True