Excel - How to
delete blank lines
I import data to Excel from other databases. I end up with a lot of blank lines and I need to delete them. The problem I had was that there was data scatted throughout the various columns and I could not use the neat sort routines that I found on the Web. This is how it works it is pretty straight forward and easy to understand.
Sub DeltBlnkRow() 'I like to save the current cell address so I can return to it C = ActiveCell.Address 'This places the last line of data into "D" D = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row 'This line turns the screen refresh off so it speeds up the process Application.ScreenUpdating = False 'Keep going while there is data While ActiveCell.Row <= D ' This routine determine which row contains no data using CountA If WorksheetFunction.CountA(Selection.EntireRow) = 0 Then ' If there is no data then the row is deleted Selection.EntireRow.Delete ' Decrement the number of rows that contain data D = D - 1 Else ' If the row is not blank go to test the next row ActiveCell.Offset(1, 0).Select End If Wend Ender: 'Go back to the original cell ActiveSheet.Range(C).Select 'Back on Application.ScreenUpdating = True End Sub