Delete Rows with Value greater than X

[STEVEB]

Hi,

Does anyone have any suggestions for a Macro that would:

Delete all rows in Sheet 2 THAT HAS A VALUE IN COLUMN A with a value greater than CELL B2 in Sheet 1?

Any help would be greatly appreciated!

[Tom Ogilvy]

Dim i as Long, rw as Long, rng as Range
Dim crit as Double
crit = worksheets("Sheet1").Range("B2").Value
With worksheets("Sheet2")
rw = .cells(rows.count,1).End(xlup).rw
for i = rw to 1 step -1
if .cells(i,1).Value > crit then
if rng is nothing then
set rng = .cells(i,1)
else
set rng = union(rng,.cells(i,1))
end if
end if
Next
End With
if not rng is nothing then
rng.EntireRow.Delete
End if

Would be one way. Others would be to use an Autofilter or put a formula in
a dummy column and use special cells.

--
Regards,
Tom Ogilvy

"STEVEB" <[email protected]> wrote in
message news:[email protected]...
>
> Hi,
>
> Does anyone have any suggestions for a Macro that would:
>
> Delete all rows in Sheet 2 THAT HAS A VALUE IN COLUMN A with a value
> greater than CELL B2 in Sheet 1?
>
> Any help would be greatly appreciated!
>
>
> --
> STEVEB
> ------------------------------------------------------------------------
> STEVEB's Profile:
http://www.excelforum.com/member.php...fo&userid=1872
> View this thread: http://www.excelforum.com/showthread...hreadid=390199
>

[STEVEB]

Hi Tom,

I was having a little trouble with the forulma:

The code has this error message:

Run Time Error '438':
Object doesn't support this property or method

At this line:
rw = .Cells(Rows.Count, 1).End(xlUp).rw

Does it matter that I am using dates?

Cell b2 in sheet 1 is 7/31/05
& all the cells in column A - Sheet 2 are dates.

Thanks for your help.

[Tom Ogilvy]

Typo:
the last rw should be row

rw = .Cells(Rows.Count, 1).End(xlUp).row


--
Regards,
Tom Ogilvy


"STEVEB" <[email protected]> wrote in
message news:[email protected]...
>
> Hi Tom,
>
> I was having a little trouble with the forulma:
>
> The code has this error message:
>
> Run Time Error '438':
> Object doesn't support this property or method
>
> At this line:
> rw = .Cells(Rows.Count, 1).End(xlUp).rw
>
> Does it matter that I am using dates?
>
> Cell b2 in sheet 1 is 7/31/05
> & all the cells in column A - Sheet 2 are dates.
>
> 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=390199
>

[STEVEB]

Thanks Tom!

Everything worked great!

I appreciate your help!