Hi

Does anyone have any suggestions for a Macro that would delete a
entire row in Sheet 2 if the value in column A Sheet 2 is equal to an
cell within the range of B2 to B50 on Sheet 1.

Thanks for your help

--
STEVE
-----------------------------------------------------------------------
STEVEB's Profile: http://www.excelforum.com/member.php...nfo&userid=187
View this thread: http://www.excelforum.com/showthread.php?threadid=39060

Do A MATCH

On Error Resume Next
ans =
Application.Match(Range("A2").Value,Worksheets("Sh eet1").Range("B2:B50"),0)
On Error Goto 0
If ans 0 Then
Rows(2).Delete
End If

--

HTH

RP
(remove nothere from the email address if mailing direct)


"STEVEB" wrote in
message ...

Hi

Does anyone have any suggestions for a Macro that would delete an
entire row in Sheet 2 if the value in column A Sheet 2 is equal to any
cell within the range of B2 to B50 on Sheet 1.

Thanks for your help!


--
STEVEB
------------------------------------------------------------------------
STEVEB's Profile:
http://www.excelforum.com/member.php...fo&userid=1872
View this thread: http://www.excelforum.com/showthread...hreadid=390607

Hi Bob,

Thanks for your help, I appreciate it!

I could not get the code to work. the problem is at:

If ans 0 Then

I am getting this error message:

Run-time error '13':
Type mismatch

Is it because I am using dates?

I have about 20 dates on Sheet 1 Range B2-B50.
On Sheet 2 in Column A I have about 175 dates (1 in each row)
I would like to delete the entire row of data if any date in column
Sheet 2 matches any of the dates in the range B2-B50 on Sheet 1.

Thanks again for your help

--
STEVE
-----------------------------------------------------------------------
STEVEB's Profile: http://www.excelforum.com/member.php...nfo&userid=187
View this thread: http://www.excelforum.com/showthread.php?threadid=39060

ans = 0
On Error Resume Next
ans = Application.WorksheetFunction.Match( _
clng(Range("A2").Value),Worksheets("Sheet1").Range ("B2:B50"),0)
On Error Goto 0
If ans 0 Then
Rows(2).Delete
End If

This deletes Row 2 on the active sheet. what you want to delete is
ambiguous.

--
Regards,
tom Ogilvy


"STEVEB" wrote in
message ...

Hi Bob,

Thanks for your help, I appreciate it!

I could not get the code to work. the problem is at:

If ans 0 Then

I am getting this error message:

Run-time error '13':
Type mismatch

Is it because I am using dates?

I have about 20 dates on Sheet 1 Range B2-B50.
On Sheet 2 in Column A I have about 175 dates (1 in each row)
I would like to delete the entire row of data if any date in column A
Sheet 2 matches any of the dates in the range B2-B50 on Sheet 1.

Thanks again for your help.


--
STEVEB
------------------------------------------------------------------------
STEVEB's Profile:
http://www.excelforum.com/member.php...fo&userid=1872
View this thread: http://www.excelforum.com/showthread...hreadid=390607

Declare it as a Long Steve.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"STEVEB" wrote in
message ...

Hi Bob,

Thanks for your help, I appreciate it!

I could not get the code to work. the problem is at:

If ans 0 Then

I am getting this error message:

Run-time error '13':
Type mismatch

Is it because I am using dates?

I have about 20 dates on Sheet 1 Range B2-B50.
On Sheet 2 in Column A I have about 175 dates (1 in each row)
I would like to delete the entire row of data if any date in column A
Sheet 2 matches any of the dates in the range B2-B50 on Sheet 1.

Thanks again for your help.


--
STEVEB
------------------------------------------------------------------------
STEVEB's Profile:
http://www.excelforum.com/member.php...fo&userid=1872
View this thread: http://www.excelforum.com/showthread...hreadid=390607

Hi Tom,

Thanks for the input, I appreciate it! I am still not getting the code
to work properly.

For example:

Column A - Sheet 2 lists every date beginning 1/1/2005 through 7/31/05.
210 rows.

Column E - Sheet 1 lists about 20 dates(1/1/2005, 6/8/2005, 5/4/2005,
12/30/2005, etc.). The range is E2:E50 (Only E2:e21 has a date in it
this month, however, so months there are up to 50 dates)

What I would like is to run a Macro that would compare Column E - Sheet
1 to Column A - Sheet 2. If Column A - Sheet 2 has any date listed in
Column E - Sheet 1, I would like to delete the entire row in Column A
Sheet 2.

I hope this helps. Thanks again


--
STEVEB
------------------------------------------------------------------------
STEVEB's Profile: http://www.excelforum.com/member.php...fo&userid=1872
View this thread: http://www.excelforum.com/showthread...hreadid=390607

Sub DeleteMatches()
dim rng as Range, rng1 as Range
Dim res as Variant, cell as Range
With Worksheets("Sheet1")
set rng = .Range(.Cells(2,"E"),.Cells(2,"E").End(xldown))
end With
for each cell in rng
With Worksheets("Sheet2")
set rng1 = .range(".Cells(2,1),.Cells(2,1).End(xldown))
End with
res = Application.Match(clng(cell),rng1,0)
if not iserror(res) then
rng1(res).EntireRow.Delete
end if
Next
End sub

--
Regards,
Tom Ogilvy



"STEVEB" wrote in
message ...

Hi Tom,

Thanks for the input, I appreciate it! I am still not getting the code
to work properly.

For example:

Column A - Sheet 2 lists every date beginning 1/1/2005 through 7/31/05.
210 rows.

Column E - Sheet 1 lists about 20 dates(1/1/2005, 6/8/2005, 5/4/2005,
12/30/2005, etc.). The range is E2:E50 (Only E2:e21 has a date in it
this month, however, so months there are up to 50 dates)

What I would like is to run a Macro that would compare Column E - Sheet
1 to Column A - Sheet 2. If Column A - Sheet 2 has any date listed in
Column E - Sheet 1, I would like to delete the entire row in Column A
Sheet 2.

I hope this helps. Thanks again


--
STEVEB
------------------------------------------------------------------------
STEVEB's Profile:
http://www.excelforum.com/member.php...fo&userid=1872
View this thread: http://www.excelforum.com/showthread...hreadid=390607