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

 

[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

 

[STEVEB]

Thanks Tom!

Everything worked great!

I appreciate your help