| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 - |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| REMOVING DATA BASED ON OTHER CELL INFORMATION | Excel Programming | |||
| removing data based on other cell information | Excel Programming | |||
| Transfer single cell information to specific cell based on user criteria | Excel Programming | |||
| Removing a Row based on a cell value | Excel Discussion (Misc queries) | |||
| Auto-fill cell based on adjacent cell information.. | Excel Worksheet Functions | |||
Hybrid Mode
