REMOVING DATA BASED ON OTHER CELL INFORMATION

[S Himmelrich]

I've created the following macro that removes data based on ofther
cell information. This is similar to duplicate row code, but I'm
looking to define which columns I evaluate. Currently, this macro
looks at just column A or 1. I need it to look at column A, C, F &
G. My question is more syntax based I believe as I'm at a beginner
level in creating VB/Macro code; otherwise this is working as needed
-
can anyone assist on the range on this one?
'Removing Dupicates
Dim rng As Range
Dim i As Integer
Set rng = Range(Cells(1, 1), Cells(1, 1).End(xlDown))
For i = rng.Rows(rng.Rows.Count).Row To rng.Row + 1 Step -1
If Cells(i, 1).Value = Cells(i - 1, 1) Then
Cells(i, 6).ClearContents
Cells(i, 7).ClearContents
Cells(i, 8).ClearContents
Cells(i, 9).ClearContents
End If
Next
SAMPLE INPUT:
Column A Column C Column D Column F Column G Column H Column I
04531 0 0 0 0.00
04531 0 0 0 0.00
04578 Ganesh 132884 32 6 6 2592
045102 Robin Uffer 137308 2 42 162
045102 0 0 0 0.00
045102 Peter Donald 134932 1 2.1111 111 81
045102 Peter Donald 134932 1 2.1111 111 81



SAMPLE OUTPUT AFTER MACRO HAS RUN:
Column A Column C Column D Column F Column G Column H Column I
04531 0 0 0 0.00
04531 cleared cleared cleared cleared
04578 Ganesh 132884 32 6 6 2592
045102 Robin Uffer 137308 2 42 162
045102 0 0 0 0.00
045102 Peter Donald 134932 1 2.1111 111 81
045102 Peter Donald 134932 cleared cleared cleared
cleared

 

[Dave Peterson]

You've posted this a few times and gotten a few (unsatisfactory) replies. I
think that means you're going to have to explain what you want in more detail.

Do you check A1 with A2, C1 with C2, F1 with F2, G1 with G2 and if all those
have corresponding matches, then you clear the contents of columns F:I of the
bottom row.

Or if one of them matches, clear all F:I.

Or what????

 

[S Himmelrich]

Thank you for responding. Yes you are pretty dead on. I'm basically
identifying duplicate rows only wanting to compare A,C,F&G (as opposed
to the whole row) and for rows that duplicate clear the information in
F,G,H & I) in the duplicate rows.

 

[Dave Peterson]

Maybe...

Option Explicit
Sub testme()
Dim iRow As Long
Dim LastRow As Long
Dim FirstRow As Long

With ActiveSheet
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = LastRow To FirstRow + 1 Step -1
'A,C,F&G
If .Cells(iRow, "A").Value = .Cells(iRow - 1, "A").Value _
And .Cells(iRow, "C").Value = .Cells(iRow - 1, "C").Value _
And .Cells(iRow, "F").Value = .Cells(iRow - 1, "F").Value _
And .Cells(iRow, "G").Value = .Cells(iRow - 1, "G").Value Then
'F,G,H&I
.Cells(iRow, "F").Resize(1, 4).ClearContents
End If
Next iRow
End With
End Sub