I have several columns of data in a worksheet (A1:I200), that is hard coded to another worksheet ie cell A1 = Sheet2!AB1, A2 = Sheet2!AB2 and so on

Sometimes there is no data in the source cells which is resulting in many blank rows in the destination worksheet.

I found the following macro that does delete blank rows, but the entire row must be empty for it to work

Public Sub DeleteBlankRows()

Dim R As Long
Dim C As Range
Dim Rng As Range

On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

If Selection.Rows.Count > 1 Then
Set Rng = Selection
Else
Set Rng = ActiveSheet.UsedRange.Rows
End If
For R = Rng.Rows.Count To 1 Step -1
If Application.WorksheetFunction.CountA(Rng.Rows(R).EntireRow) = 0 Then
Rng.Rows(R).EntireRow.Delete
End If
Next R

EndMacro:

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

I am able to get rid of the formula’s by doing a paste value, however the cells are obviously not truly empty as the macro is not working. (If I manually select the cells and press the delete key the macro works)

Does anyone know a way of a macro that I can use to clear the cell contents of these cells, or any suggestions on how to get rid if these blank unwanted rows?

Thanks
Karen