Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Repititions
Hi,
I have a list of 100 values in Column B, I need to delete repetitions, the following code needs tweaking, help is much appreciated Sub del() Dim i, j I<J For i = 1 To 10 For j = 1 To 10 If Cells(i, 2) = Cells(j, 2) Then Cells(i, 2).EntireRow.Delete End If Next Next End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Repititions
This is untested.
Sub DupeClear() Dim r As Long Dim c As Long Dim x as Variant Dim y as Variant r = 1 c = 2 Do Until Cells(r, c).Value = "" x = Cells(r, c).Value If x = y Then Rows(r).EntireRow.Delete Else r = r + 1 End If y = x Loop End Sub HTH Otto "teresa" wrote in message ... Hi, I have a list of 100 values in Column B, I need to delete repetitions, the following code needs tweaking, help is much appreciated Sub del() Dim i, j I<J For i = 1 To 10 For j = 1 To 10 If Cells(i, 2) = Cells(j, 2) Then Cells(i, 2).EntireRow.Delete End If Next Next End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Repititions
Sub test()
Const cCol = 2 Dim i As Long, j As Long, rng As Range i = 1: j = 100 Do Until i j Set rng = Cells(i, cCol) If WorksheetFunction.CountIf(Range(rng, Cells(j, cCol)), rng) 1 Then rng.EntireRow.Delete xlShiftUp j = j - 1 Else i = i + 1 End If Loop End Sub -- Rob van Gelder - http://www.vangelder.co.nz/excel "teresa" wrote in message ... Hi, I have a list of 100 values in Column B, I need to delete repetitions, the following code needs tweaking, help is much appreciated Sub del() Dim i, j I<J For i = 1 To 10 For j = 1 To 10 If Cells(i, 2) = Cells(j, 2) Then Cells(i, 2).EntireRow.Delete End If Next Next End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Repititions
How about this?
Range("A1:A100").AdvancedFilter _ Action:=xlFilterInPlace, Unique:=True *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Repititions
Folks,
After seeing Rob van Gelder's solution, my solution looks rather "quaint":) Cheers, A. London __________________________________________________ __ Sub DelDup() 'Eliminate Rows with duplicate entries in Col A and sort Dim I, J, theRows As Integer Dim TestValue As String Dim theList As Range 'Initialize theRows = 50 'Number of Rows to eliminate Dups Set theList = Range("A1:A" & theRows) 'Column A 'Eliminate Duplicates For I = 1 To theList.Count TestValue = theList.Cells(I, 1) For J = I + 1 To theList.Count If TestValue = theList.Cells(J, 1) Then Rows(J).Delete J = J - 1 End If Next J Next I 'Cleanup Columns("A:AZ").Select Selection.Sort Key1:=Range("A1"), Order1:=xlAscending Range("A1").Select End Sub "teresa" wrote in message ... Hi, I have a list of 100 values in Column B, I need to delete repetitions, the following code needs tweaking, help is much appreciated Sub del() Dim i, j I<J For i = 1 To 10 For j = 1 To 10 If Cells(i, 2) = Cells(j, 2) Then Cells(i, 2).EntireRow.Delete End If Next Next End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting the text without deleting the formula | Excel Worksheet Functions | |||
Deleting cell data without deleting formula | Excel Discussion (Misc queries) | |||
deleting values in a worksheet without deleting the formulas | Excel Worksheet Functions | |||
how prevent formula in cell from deleting when deleting value???? | New Users to Excel | |||
Deleting Hyphens or Dashes from multiple cells without deleting the remaining content | Excel Programming |