Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy rows if greater than zero | Excel Worksheet Functions | |||
How to delete rows when List toolbar's "delete" isnt highlighted? | Excel Worksheet Functions | |||
Macro if greater than letter then delete | Excel Programming | |||
Extract and copy Rows where value is Greater than 0 (zero) | Excel Programming | |||
Delete every 3rd row, then delete rows 2-7, move info f/every 2nd row up one to the end and delete the row below | Excel Programming |