Delete Rows with Value greater than X
 

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 valu
greater than CELL B2 in Sheet 1?

Any help would be greatly appreciated

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

Delete Rows with Value greater than X
 

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" wrote in
message ...

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

Delete Rows with Value greater than X
 

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

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

Delete Rows with Value greater than X
 

Typo:
the last rw should be row

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


--
Regards,
Tom Ogilvy


"STEVEB" wrote in
message ...

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

Delete Rows with Value greater than X
 

Thanks Tom!

Everything worked great!

I appreciate 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=39019