Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clearing Contents
I have a workbook open with about 22,000 rows in it. It has data like this
A B C D SD FRED 3 9 SD FRED 4 2 SD FRED 6 27 SF SAM 3 6 SF SAM 4 8 SF SAM 6 1 Now A and B continually shows the same info and C and D are what changes, I want to clear the contents on columns A and B if it sees the same in A and B twice, before jumping to the next row. So the data would look like this when through A B C D SD FRED 3 9 4 2 6 27 SF SAM 3 6 4 8 6 1 Same data but it only shows me the vendor and the name once and then continues on with the rest of the data. Any ideas anyone!!! Thanks in advance Sincerely Sean |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clearing Contents
How about doing it with formulas?
Insert 2 more columns c + d formula in c2 = if(a2<a1,a2,"") formula in d2 = if (b2<b1,b2,"") Copy these down and you can then hide A and B to display your required output. Chris "Sean" wrote in message ... I have a workbook open with about 22,000 rows in it. It has data like this A B C D SD FRED 3 9 SD FRED 4 2 SD FRED 6 27 SF SAM 3 6 SF SAM 4 8 SF SAM 6 1 Now A and B continually shows the same info and C and D are what changes, I want to clear the contents on columns A and B if it sees the same in A and B twice, before jumping to the next row. So the data would look like this when through A B C D SD FRED 3 9 4 2 6 27 SF SAM 3 6 4 8 6 1 Same data but it only shows me the vendor and the name once and then continues on with the rest of the data. Any ideas anyone!!! Thanks in advance Sincerely Sean |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clearing Contents
Sean,
Try this: Sub RemoveAB() Dim ilastrow As Long, i As Long ilastrow = Cells(Rows.Count, "A").End(xlUp).Row For i = ilastrow To 1 Step -1 If Cells(i - 1, "A") = Cells(i, "A") And _ Cells(i - 1, "B") = Cells(i, "B") Then Cells(i, "A") = "" Cells(i, "B") = "" End If Next i End Sub HTH "Sean" wrote: I have a workbook open with about 22,000 rows in it. It has data like this A B C D SD FRED 3 9 SD FRED 4 2 SD FRED 6 27 SF SAM 3 6 SF SAM 4 8 SF SAM 6 1 Now A and B continually shows the same info and C and D are what changes, I want to clear the contents on columns A and B if it sees the same in A and B twice, before jumping to the next row. So the data would look like this when through A B C D SD FRED 3 9 4 2 6 27 SF SAM 3 6 4 8 6 1 Same data but it only shows me the vendor and the name once and then continues on with the rest of the data. Any ideas anyone!!! Thanks in advance Sincerely Sean |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clearing Contents
Sorry .. typo .. should be
For i = ilastrow To 2 Step -1 "Toppers" wrote: Sean, Try this: Sub RemoveAB() Dim ilastrow As Long, i As Long ilastrow = Cells(Rows.Count, "A").End(xlUp).Row For i = ilastrow To 1 Step -1 If Cells(i - 1, "A") = Cells(i, "A") And _ Cells(i - 1, "B") = Cells(i, "B") Then Cells(i, "A") = "" Cells(i, "B") = "" End If Next i End Sub HTH "Sean" wrote: I have a workbook open with about 22,000 rows in it. It has data like this A B C D SD FRED 3 9 SD FRED 4 2 SD FRED 6 27 SF SAM 3 6 SF SAM 4 8 SF SAM 6 1 Now A and B continually shows the same info and C and D are what changes, I want to clear the contents on columns A and B if it sees the same in A and B twice, before jumping to the next row. So the data would look like this when through A B C D SD FRED 3 9 4 2 6 27 SF SAM 3 6 4 8 6 1 Same data but it only shows me the vendor and the name once and then continues on with the rest of the data. Any ideas anyone!!! Thanks in advance Sincerely Sean |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clearing Contents
Sean:
You could use a loop to move through the worksheet. Within the loop, compare the values in column A and column B using the ActiveCell.Offset property. If A& B are the same, then you can set the values of the second row to "". I tested the code below and it appears to work. For example: Sub TestMacro() Dim wks As Worksheet Dim colA As String Dim colB As String Dim i As Integer Set wks = ActiveSheet Cells(1, 1).Select For i = 1 To wks.Cells.CurrentRegion.Rows.Count colA = ActiveCell.Value colB = ActiveCell.Offset(0, 1).Value Do Until colA < ActiveCell.Offset(1, 0).Value And colB < ActiveCell.Offset(1, 1).Value ActiveCell.Offset(1, 0) = "" ActiveCell.Offset(1, 1) = "" ActiveCell.Offset(1, 0).Select i = i + 1 Loop ActiveCell.Offset(1, 0).Select Next i -- David Lloyd MCSD .NET http://LemingtonConsulting.com This response is supplied "as is" without any representations or warranties. "Sean" wrote in message ... I have a workbook open with about 22,000 rows in it. It has data like this A B C D SD FRED 3 9 SD FRED 4 2 SD FRED 6 27 SF SAM 3 6 SF SAM 4 8 SF SAM 6 1 Now A and B continually shows the same info and C and D are what changes, I want to clear the contents on columns A and B if it sees the same in A and B twice, before jumping to the next row. So the data would look like this when through A B C D SD FRED 3 9 4 2 6 27 SF SAM 3 6 4 8 6 1 Same data but it only shows me the vendor and the name once and then continues on with the rest of the data. Any ideas anyone!!! Thanks in advance Sincerely Sean |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clearing Contents
Sean,
Actually clearing the contents is a bad idea. Data is good: blanks are bad. Anyway, if you want to not see the contents, select cells A2:B22000, select Format | Conditional formatting... Then select "Formula is" and use the formula =A2=A1 Set your font to white (or whatever background color you have) and you won't see the value in the cell, but will still have the data. HTH, Bernie MS Excel MVP "Sean" wrote in message ... I have a workbook open with about 22,000 rows in it. It has data like this A B C D SD FRED 3 9 SD FRED 4 2 SD FRED 6 27 SF SAM 3 6 SF SAM 4 8 SF SAM 6 1 Now A and B continually shows the same info and C and D are what changes, I want to clear the contents on columns A and B if it sees the same in A and B twice, before jumping to the next row. So the data would look like this when through A B C D SD FRED 3 9 4 2 6 27 SF SAM 3 6 4 8 6 1 Same data but it only shows me the vendor and the name once and then continues on with the rest of the data. Any ideas anyone!!! Thanks in advance Sincerely Sean |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Clearing Contents | Excel Discussion (Misc queries) | |||
Clearing contents not formatting | Excel Discussion (Misc queries) | |||
Clearing Contents of Cell | Excel Worksheet Functions | |||
Clearing Contents but not Formulas | Excel Worksheet Functions | |||
Clearing Cell Contents | Excel Programming |