Comparing active cell to the value in another cell
 

Hi,

I am trying to set up a macro that will compate the active cell i
Sheet 1 column AD against the value in Sheet 2 cell reference A2. I
the active cell matches the value in Sheet 2 cell A2 the row will b
ignored and the active cell will move down a row. If the active cel
does not match the value in Sheet 2 cell A2 the whole row will b
deleted. This should repeat until a blank row is found in Sheet 1.
think I am looking for something like the code below:

Range("AD:AD").Select

Do While ActiveCell < 0

Test = (ActiveCell) = (Sheet2!A2)
If Test = True Then
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.EntireRow.Delete
End If

Loop

The problem is that "Test = (ActiveCell) = (Sheet2!A2)" doesn't appea
to compare the two values.

Can anyone help?

Thanks
Jame

--
JamesArchibal
-----------------------------------------------------------------------
JamesArchibald's Profile: http://www.excelforum.com/member.php...fo&userid=3328
View this thread: http://www.excelforum.com/showthread.php?threadid=56321

Comparing active cell to the value in another cell
 

My suggestion would be to read the value of the sheet2 A2 cell into a
variable and then check each cell in AD as you go ...

Dim strCompare as String
Dim strCurrent as string
Dim intCounter as integer

intcounter = 1

sheets(2).activate

strcompare = range("A2").Value

sheets(1).Activate

do while len(range("AD" & intcounter).text) 0

strcurrent = range("AD" & intcounter).value

If strcurrent = strcompare then

intcounter = intcounter + 1

else

Rows(intcounter & ":" & intcounter).Delete Shift:=xlUp

end if

loop

The code is untested and written in here, so you may need to che cit.

HTH.

"JamesArchibald" wrote:


Hi,

I am trying to set up a macro that will compate the active cell in
Sheet 1 column AD against the value in Sheet 2 cell reference A2. If
the active cell matches the value in Sheet 2 cell A2 the row will be
ignored and the active cell will move down a row. If the active cell
does not match the value in Sheet 2 cell A2 the whole row will be
deleted. This should repeat until a blank row is found in Sheet 1. I
think I am looking for something like the code below:

Range("AD:AD").Select

Do While ActiveCell < 0

Test = (ActiveCell) = (Sheet2!A2)
If Test = True Then
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.EntireRow.Delete
End If

Loop

The problem is that "Test = (ActiveCell) = (Sheet2!A2)" doesn't appear
to compare the two values.

Can anyone help?

Thanks
James


--
JamesArchibald
------------------------------------------------------------------------
JamesArchibald's Profile: http://www.excelforum.com/member.php...o&userid=33284
View this thread: http://www.excelforum.com/showthread...hreadid=563212

Comparing active cell to the value in another cell
 

Thanks for your help. Just tried your code there and it works perfectly.
That's one less thing on my to do list for today!

James


--
JamesArchibald
------------------------------------------------------------------------
JamesArchibald's Profile: http://www.excelforum.com/member.php...o&userid=33284
View this thread: http://www.excelforum.com/showthread...hreadid=563212

Comparing active cell to the value in another cell
 

No probs, just glad it worked without testing it.

If you found this helpful, would you mind indicating this on the buttons at
the bottom of the post.

HTH.

"JamesArchibald" wrote:


Thanks for your help. Just tried your code there and it works perfectly.
That's one less thing on my to do list for today!

James


--
JamesArchibald
------------------------------------------------------------------------
JamesArchibald's Profile: http://www.excelforum.com/member.php...o&userid=33284
View this thread: http://www.excelforum.com/showthread...hreadid=563212

Comparing active cell to the value in another cell
 

Do you mean like this Thumbs up one?


--
JamesArchibald
------------------------------------------------------------------------
JamesArchibald's Profile: http://www.excelforum.com/member.php...o&userid=33284
View this thread: http://www.excelforum.com/showthread...hreadid=563212