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

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

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

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

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

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