Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete duplicate Name but not the blank rows
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).E ntireRow) < 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 -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete duplicate Name but not the blank rows
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 -- Message posted from http://www.ExcelForum.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete duplicate Name but not the blank rows
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 -- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete duplicate Name but not the blank rows
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/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete duplicate Name but not the blank rows
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 -- Message posted from http://www.ExcelForum.com |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete duplicate Name but not the blank rows
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. -- Regards, Tom Ogilvy "kaon " wrote in message ... Thank Tom for your great help. No idea why the macro still keeps deleting truly blank rows! (Does the border count?) And I found how I change the for-loop loops from 'last-to-first' or '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! --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
delete duplicate rows, keep one | Excel Discussion (Misc queries) | |||
delete duplicate rows | Excel Worksheet Functions | |||
HELP delete duplicate rows. | Excel Programming | |||
Delete duplicate rows | Excel Programming | |||
delete duplicate rows | Excel Programming |