REMOVING DATA BASED ON OTHER CELL INFORMATION
 

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

REMOVING DATA BASED ON OTHER CELL INFORMATION
 

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 wrote:

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

REMOVING DATA BASED ON OTHER CELL INFORMATION
 

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.


On Sep 12, 12:05*pm, Dave Peterson wrote:
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 wrote:

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- Hide quoted text -

- Show quoted text -

REMOVING DATA BASED ON OTHER CELL INFORMATION
 

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




S Himmelrich wrote:

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.

On Sep 12, 12:05 pm, Dave Peterson wrote:
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 wrote:

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- Hide quoted text -

- Show quoted text -

--

Dave Peterson