The problem is with the UsedRange that Excel tracks... under certain
conditions, it doesn't get updated to reflect the actually used range of
data. I think this macro will fix the problem you are having (provided your
data never goes down to the very last row on the worksheet)...

Sub FixUsedRange()
Dim LastRow As Long
With ActiveSheet
LastRow = .Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious).Row
.Cells(LastRow + 1, 1).Value = "X"
.Range(.Rows(LastRow + 1), .Rows(.UsedRange.Rows.Count)).Delete
End With
End Sub

--
Rick (MVP - Excel)


"RLN" wrote in message
...
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.