How to use looping to delete cells of similar value
Category: [Excel] Demo Available
How to use looping to delete cells of similar value
Requirement:
The user is looping through rows by looking only at I column and J column contents. If there are more than 2 rows similar based on I and J value then the user deletes the rest of rows. ( 2 consecutive values in I and J can be similar but not more than that).
The problem the user is facing is that it doesnt take into account the name (column I) and just take into account the values in Column J. The user wants if the current cell and the cells up and below are similar then delete the row.
The code that I am currently using is as follows:
Code:
Dim rowcnt As Long
Dim ia As Long
Dim ja As Long
Dim jaa As Long
rowcnt = Cells(Rows.Count, "H").End(xlUp).Row
For ia = 2 To rowcnt
ja = ia - 1
jaa = ia + 1
If Range("J" & ia).Value = Range("J" & ja).Value And Range("J" & ja).Value = Range("J" & jaa).Value And _
Range("I" & ia).Value = "SCATTER 20X20" And Range("I" & ja).Value = "SCATTER 20X20" And Range("I" & ja).Value = "SCATTER 20X20" Then
Range("G" & jaa, "M" & jaa).Delete
End If
Next ia
Solution:
Code:
Private Sub CommandButton1_Click()
Dim x, i&, ii&, r As Range, U As Range
Set r = [G1].CurrentRegion
x = r.Resize(r.Rows.Count + 2).Value: On Error Resume Next
For i = 2 To UBound(x) - 2
If x(i, 2) = x(i + 1, 2) And x(i, 2) = x(i - 1, 2) Then
If x(i, 3) = "SCATTER 20X20" And x(i + 1, 3) = "SCATTER 20X20" And x(i - 1, 3) = "SCATTER 20X20" Then
Set U = Union(IIf(U Is Nothing, r(i + 1, 1).Resize(, 7), U), r(i + 1, 1).Resize(, 7))
End If
End If
Next i
If Not U Is Nothing Then
MsgBox U.Address
U.Delete
End If
End Sub
Obtained from the OzGrid Help Forum.
Solution provided by graha_karya.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to create repeated cell values |
| How to use a macro to run through sheet in excel and put double quotes around values |
| How to Auto populate cells with existing values |
| How to use a macro to select value criteria from a table rather than manually inputting |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.