Sub AAAA()
Dim rng As Range, r As Long, v As Variant
Dim N As Long
Set rng = Range(Range("A1"), Cells(Rows.Count, 1).End(xlUp))
For r = rng.Rows.Count To 1 Step -1
v = rng.Cells(r, 1).Value
If Application.WorksheetFunction. _
CountIf(rng.Columns(1), v) 1 And _
Application.WorksheetFunction. _
CountA(rng.Rows(r).EntireRow) < 0 Then
rng.Rows(r).EntireRow.Delete
N = N + 1
End If
Next r

End Sub

worked for me. It won't delete truly blank cells. If you cells have
formula like

=if(condition,"",formula)

so it is displaying an empty string, this cell is not blank and will be
counted by CountA.

CountA counts cells containing anything. Count counts cells that contain
numbers.

--
Regards,
Tom Ogilvy

"kaon " wrote in message
...
Hi all,

I am a newbie in excel marco and this forum (from Hong Kong). :)
I used the macro from cpearson.com, but slightly modified to suit my
need.

Here is the modification:

For r = Rng.Rows.Count To 1 Step -1
V = Rng.Cells(r, 1).Value
If Application.WorksheetFunction.CountIf(Rng.Columns( 1), V) 1 And
_
Application.WorksheetFunction.CountA(Rng.Rows(r).E ntireRow) < 0
Then
Rng.Rows(r).EntireRow.Delete
N = N + 1
End If
Next r

I have no idea why this would also delete those blank rows.

Also, how to use CountA()? I mean the difference between count and
countA, coz I cannot find any from google.

Thanks.


---
Message posted from http://www.ExcelForum.com/