View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
RLN[_2_] RLN[_2_] is offline
external usenet poster
 
Posts: 8
Default Deleting Blank Rows at end of sheet

Excel 2003 SP3 / WinXP SP3

Some Excel sheets I recieve from other departments in house have blank
rows at the end of their actual data.
I had a sheet recently that had 35 rows of actual data. It bombed on
a blank row when imported to another system. I did <ctrl<home then
<ctrl<end on the sheet and found I was sitting on row 359!

Below is a routine I came up with to delete all blank rows below the
last valid data row. It takes about 15 seconds to run. Always
seeking more efficient ways to do stuff, I know there are some Excel
MVP's out here. So if there is a better/faster/leaner way to
accomplish this task than what I have posted here, I would welcome the
solution.

Sub DeleteBlankRowsOnly()
'delete all blank rows below the last valid row.
Dim lastrow As Long
Dim row_index As Long
Application.ScreenUpdating = False
lastrow = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row

'65536 row limit in Excel2003...
For row_index = 65537 - 1 To 1 Step -1
If Cells(row_index, 1).Value = "" Then
Rows(row_index).Delete
End If
Next
Application.ScreenUpdating = True
ActiveWorkbook.Save
End Sub


Thanks.