Hi indhaka,
Though trying to keep null formulas complicates things, try this (initially on a copy of your data as the results cannot be undone if they're not as expected) while on the sheet in question:
Option Explicit
Sub Macro3()
'http://www.excelforum.com/excel-programming/842350-delete-empty-rows.html
Dim lngMyCol As Long, _
lngMyRow As Long
lngMyCol = Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column + 1
lngMyRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Application.ScreenUpdating = False
With Columns(lngMyCol)
With Range(Cells(2, lngMyCol), Cells(lngMyRow, lngMyCol)) 'Starts from Row 2. Change to suit.
.Formula = "=IF(LEN(A2)+LEN(B2)+--ISTEXT(A2)+--ISTEXT(B2)=0,""DEL"","""")" 'Starts from Row 2. Change to suit.
.Value = .Value
End With
.Replace "DEL", "#N/A", xlWhole
On Error Resume Next 'Turn error reporting off - OK to ignore 'No cells found' message
.SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
On Error GoTo 0 'Turn error reporting back on
.Delete
End With
Application.ScreenUpdating = True
End Sub
HTH
Robert
Bookmarks