R
RLN
RE: 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.
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.