Delete Cells Based on Range

[STEVEB]

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!

[Bob Phillips]

Do A MATCH

On Error Resume Next
ans =
Application.Match(Range("A2").Value,Worksheets("Sheet1").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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[STEVEB]

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.

[Tom Ogilvy]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Bob Phillips]

Declare it as a Long Steve.

--

HTH

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


"STEVEB" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[STEVEB]

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

[Tom Ogilvy]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[STEVEB]

Tom,

Thanks so much for your help, I appreciate it!

Everything went smooth!!!

Thanks again!!!