Sub Button1_Click()
mRow = 1
nCnt = 0
While True
If Trim(Sheet1.Cells(mRow, 1)) = "" Then
Sheet1.Cells(mRow, 1).Delete
nCnt = nCnt + 1
If nCnt = 10 Then 'If 10 contineous blank cells will stop the execution.
nCnt = 0
Exit Sub
End If
Else
mRow = mRow + 1
End If
Wend
End Sub