Prev: how do I create a macro to select all colored cells in a worksheet
Next: Automatically hide unused columns
From: kix on 31 Jan 2010 18:34
Column a has data, column b has data.
I want ap1 to equal q1 if a1 equals b1.
Actually I am looking for a way to eliminate rows if data in one column
appears in
a second column.
Thank you
From: OssieMac on 31 Jan 2010 20:44
Your question is confusing. Do you simply want to delete rows where the value
in column A equals value in column B?

I don't see the relevance of making ap1 to equal q1 if you are deleting the
row anyway where you have 2 equal values. Might as well compare A and B and
delete.

Anyway the following is a sample of deleting rows based on a condition. It
firstly sets the interior color of the rows to yellow and then deletes all
rows with interior color yellow. It has an Exit Sub after setting the color
to let you ensure that the correct rows are going to be deleted. If you are
satisfied that the correct rows are identified, then remove the Exit Sub from
the code and re-run.

Note that a space and underscore at the end of a line is a line break in an
otherwise single line of code.

Sub DeleteRows()

Dim rngA As Range
Dim cel As Range
Dim lngRow As Long
Dim i As Long

'Assumes column headers so starts row 2
'Edit "Sheet1" to your sheet name
With Sheets("Sheet1")
lngRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set rngA = .Range(.Cells(2, "A"), _
.Cells(lngRow, "A"))

For Each cel In rngA
If cel.Value = cel.Offset(0, 1).Value Then
cel.EntireRow.Interior.ColorIndex = 6
End If
Next cel

'*********************************
'remove this section between the asterisks
'if the correct rows are yellow and re-run
Exit Sub
'*********************************

'Must work backwards when deleting rows
For i = lngRow To 2 Step -1
If .Cells(i, "A") _
.Interior.ColorIndex = 6 Then

.Cells(i, "A").EntireRow.Delete
End If
Next i

End With
End Sub

--
Regards,

OssieMac


 | 
Pages: 1
Prev: how do I create a macro to select all colored cells in a worksheet
Next: Automatically hide unused columns