Delete duplicate Name but not the blank rows

K

kaon

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 m
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 An
_
Application.WorksheetFunction.CountA(Rng.Rows(r).EntireRow) <>
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 an
countA, coz I cannot find any from google.

Thanks
 
S

Simon Lloyd

Try this for finding and deleting duplicates!

Sub RemoveDuplicateCells()
Dim Myrange As Range
Dim C As Range
Dim DelRange As Range
Dim FindRange
Set Myrange = Intersect(ActiveSheet.UsedRange, Columns("D"))
If Myrange Is Nothing Then Exit Sub
Application.ScreenUpdating = False
FindRange = Array("D", "D", "D")
For Each elem In FindRange
Set C = Myrange.Find(elem, Myrange.Cells(1), xlValues, xlPart)
If Not C Is Nothing Then
If DelRange Is Nothing Then Set DelRange = Rows(C.Row)
firstaddress = C.Address
Do
Set C = Myrange.FindNext(C)
Set DelRange = Union(DelRange, Rows(C.Row))
Loop While firstaddress <> C.Address
End If
Next
Application.ScreenUpdating = True
If DelRange Is Nothing Then Exit Sub
DelRange.Delete shift:=xlUp
End Sub


Public Sub Delete_In_H()
Const MyColumn As Integer = 4
Dim lngRow As Integer
Dim maxRow As Integer

maxRow = Cells(ActiveSheet.Rows.Count, MyColumn).End(xlUp).Row

For lngRow = maxRow To 1 Step -1
If Cells(lngRow, MyColumn).Value = "D" Then

Cells(lngRow, 3).Delete
Cells(lngRow, 4).Delete
Cells(lngRow, 5).Delete
Cells(lngRow, 6).Delete
Cells(lngRow, 7).Delete

End If
Next lngRow
End Sub



Hope this helps!

Simo
 
K

kaon

But do I need to make any modification since it deletes nothing.

Thank Simon for your help.

I want to write a macro so that rows with duplicate names (row with th
name and no data in the cells of that row) can be removed but not th
blank rows
 
T

Tom Ogilvy

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.
 
K

kaon

Thank Tom for your great help.

No idea why the macro still keeps deleting truly blank rows! (Does th
border count?)

And I found how I change the for-loop loops from 'last-to-first' o
'first-to-last', it deletes duplicate rows with my data away.

Example:
A) Depre 123 123 124
B) Reven 123
C) Depre

I want to delete row C, but it keeps deleting row A!

What a mystery
 
T

Tom Ogilvy

All I can tell you is that as I posted it, that it did not delete truly
blank rows for me and as written, row C would be deleted and Row A retained.
 
Top