Comparing active cell to the value in another cell

[JamesArchibald]

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

[DaveO]

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
>
>

[JamesArchibald]

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

[DaveO]

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
>
>

[JamesArchibald]

Do you mean like this Thumbs up one?