craigers77
08-20-2008, 02:05 PM
I have a simple loop to delete duplicate rows if a couple values in the row are the same. It works great but I have 6 sets of "options" to go through. I am using named ranges "Options2", "Options3", etc. Is there a way to loop throught the routine and change the name each time (like make the last # of the name a wildcard) or will I just have to continue showing the code 6 times (works fine but clutters up the sub). Thanks.
Here is a section of the code..
-----------------------------------------
Options2.Cells(1, 1).Select
X = ActiveCell.Row
Y = X + 1
Do While Cells(X, 11).Value <> ""
Do While Cells(Y, 11).Value <> ""
If (Cells(X, 7).Value = Cells(Y, 7).Value) And (Cells(X, 8).Value = Cells(Y, 8).Value) Then
Cells(Y, 8).EntireRow.Delete
Else
Y = Y + 1
End If
Loop
X = X + 1
Y = X + 1
Loop
Options3.Cells(1, 1).Select
X = ActiveCell.Row
Y = X + 1
Do While Cells(X, 11).Value <> ""
Do While Cells(Y, 11).Value <> ""
If (Cells(X, 7).Value = Cells(Y, 7).Value) And (Cells(X, 8).Value = Cells(Y, 8).Value) Then
Cells(Y, 8).EntireRow.Delete
Else
Y = Y + 1
End If
Loop
X = X + 1
Y = X + 1
Loop
Here is a section of the code..
-----------------------------------------
Options2.Cells(1, 1).Select
X = ActiveCell.Row
Y = X + 1
Do While Cells(X, 11).Value <> ""
Do While Cells(Y, 11).Value <> ""
If (Cells(X, 7).Value = Cells(Y, 7).Value) And (Cells(X, 8).Value = Cells(Y, 8).Value) Then
Cells(Y, 8).EntireRow.Delete
Else
Y = Y + 1
End If
Loop
X = X + 1
Y = X + 1
Loop
Options3.Cells(1, 1).Select
X = ActiveCell.Row
Y = X + 1
Do While Cells(X, 11).Value <> ""
Do While Cells(Y, 11).Value <> ""
If (Cells(X, 7).Value = Cells(Y, 7).Value) And (Cells(X, 8).Value = Cells(Y, 8).Value) Then
Cells(Y, 8).EntireRow.Delete
Else
Y = Y + 1
End If
Loop
X = X + 1
Y = X + 1
Loop