Macro can see the formulas and thus cells are not empty

Hi guys,

Here's the macro:

=========================================================
Sub Macro1()

Dim i As Long

'Range("A9:J2000").Select

For i = Selection.Rows.Count To 1 Step -1
If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then
Selection.Rows(i).EntireRow.Delete
End If
Next i

End Sub
=========================================================

..unfortunately cells with no values but only with formulas in them are being treated as non-content-free and are not being removed.

Generally what I'm looking for is a quickest macro to remove all rows that are empty (not even single cell in the row has any value), any help very appreciated.

Try this:

Sub Macro1()

Dim i As Long
Range("A9:J2000").Select

For i = Selection.Rows.Count To 1 Step -1
RowValues = Rows(i).Value
For Each StrValue In RowValues
    If StrValue <> "" Then
    Rows(i).Delete
    Exit For
    End If
Next StrValue
Next i

End Sub

Thanks for reply, unfortunately it deletes wrong rows and also rows outside the defined range (4 rows above row 9).

Sorry, thought you wanted to delete the rows with values.
Changed it to the ones with no values.
Above 9 is solved by setting the loop to 9, change if you need to.

Sub Macro1()

Dim i As Long
Range("A9:J2000").Select

For i = Selection.Rows.Count To 9 Step -1
    'Get all values from row
        RowValues = Rows(i).Value
    'Loop through values and combine them into 1
        For Each StrValue In RowValues
            CombinedRowValues = CombinedRowValues & StrValue
        Next StrValue
    'If the combined value is still empty, delete the row
        If CombinedRowValues = "" Then
            Rows(i).Delete
        End If
    'Reset value
        CombinedRowValues = ""
Next i

End Sub

Yeah that works fine - thanks a lot.

But.. your method takes same amount of time to sort out given range as method I am using currently. They are both quite slow.

My goal is to find much faster method.