NoSparks -- Your comment inspired me to look for (and find) code that resets the used range when data is copied from another source (that last bit is important). This works:
Code:
Sub Delete_Empty_Row()
Application.ScreenUpdating = False
For Each usedrng In ActiveSheet.UsedRange
If usedrng.MergeCells = True Then
If usedrng.Value = "" Then
usedrng.Value = ""
End If
Else
If usedrng.Value = "" Then
usedrng.ClearContents
End If
End If
Next
ActiveSheet.UsedRange
usedRangeLastColNum = ActiveSheet.UsedRange.Columns.Count
usedrangelastrow = ActiveSheet.UsedRange.Rows.Count
For r = usedrangelastrow To 1 Step -1
If Application.WorksheetFunction.CountA(Cells(r, usedRangeLastColNum).EntireRow) <> 0 Then
Exit For
Else
Cells(r, usedRangeLastColNum).EntireRow.Delete
End If
Next r
For c = usedRangeLastColNum To 1 Step -1
If Application.WorksheetFunction.CountA(Cells(1, c).EntireColumn) <> 0 Then
Exit For
Else
Cells(1, c).EntireColumn.Delete
End If
Next c
ActiveSheet.UsedRange
Application.ScreenUpdating = True
End Sub