Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete row 2 cell criteria
I have a spread sheet that changes nightly. In column D every cell has a
number. In column E there are either notes or it is blank. What I am looking to do is delete any row, where the number in column D is less than 96 AND there are notes in column E. So if D is higher than 96 and/or E is blank, I want to keep that row, delete all others. On any given day I may have as few as 50 rows up to 250 rows. Thanks in advance for any help. -- Thanks Steve |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete row 2 cell criteria
you can give this a try and see if it meets your needs:
Sub dlete_rows() Dim ws As Worksheet Dim i As Long Dim lastrow As Long Set ws = Worksheets("Sheet1") lastrow = ws.Cells(Rows.Count, "D").End(xlUp).Row For i = lastrow To 2 Step -1 With ws.Range("D" & i) If .Value < 96 And .Offset(, 1).Value "" Then .EntireRow.Delete End If End With Next End Sub -- Gary "Steve Kellogg" wrote in message ... I have a spread sheet that changes nightly. In column D every cell has a number. In column E there are either notes or it is blank. What I am looking to do is delete any row, where the number in column D is less than 96 AND there are notes in column E. So if D is higher than 96 and/or E is blank, I want to keep that row, delete all others. On any given day I may have as few as 50 rows up to 250 rows. Thanks in advance for any help. -- Thanks Steve |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete row 2 cell criteria
or this:
Sub dlete_rows() Dim ws As Worksheet Dim i As Long Dim lastrow As Long Set ws = Worksheets("Sheet1") lastrow = ws.Cells(Rows.Count, "D").End(xlUp).Row For i = lastrow To 2 Step -1 With ws.Range("D" & i) If .Value < 96 And len(.Offset(, 1).Value) =0 Then .EntireRow.Delete End If End With Next End Sub -- Gary "Gary Keramidas" <GKeramidasATmsn.com wrote in message ... you can give this a try and see if it meets your needs: Sub dlete_rows() Dim ws As Worksheet Dim i As Long Dim lastrow As Long Set ws = Worksheets("Sheet1") lastrow = ws.Cells(Rows.Count, "D").End(xlUp).Row For i = lastrow To 2 Step -1 With ws.Range("D" & i) If .Value < 96 And .Offset(, 1).Value "" Then .EntireRow.Delete End If End With Next End Sub -- Gary "Steve Kellogg" wrote in message ... I have a spread sheet that changes nightly. In column D every cell has a number. In column E there are either notes or it is blank. What I am looking to do is delete any row, where the number in column D is less than 96 AND there are notes in column E. So if D is higher than 96 and/or E is blank, I want to keep that row, delete all others. On any given day I may have as few as 50 rows up to 250 rows. Thanks in advance for any help. -- Thanks Steve |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete row 2 cell criteria
Gary THANK YOU!!!!! My only other question would be can I change "sheet1" to
be any sheet I am on? The sheet name is different each day. Again, I can't thank you enough, it worked perfect! -- Thanks Steve "Gary Keramidas" wrote: you can give this a try and see if it meets your needs: Sub dlete_rows() Dim ws As Worksheet Dim i As Long Dim lastrow As Long Set ws = Worksheets("Sheet1") lastrow = ws.Cells(Rows.Count, "D").End(xlUp).Row For i = lastrow To 2 Step -1 With ws.Range("D" & i) If .Value < 96 And .Offset(, 1).Value "" Then .EntireRow.Delete End If End With Next End Sub -- Gary "Steve Kellogg" wrote in message ... I have a spread sheet that changes nightly. In column D every cell has a number. In column E there are either notes or it is blank. What I am looking to do is delete any row, where the number in column D is less than 96 AND there are notes in column E. So if D is higher than 96 and/or E is blank, I want to keep that row, delete all others. On any given day I may have as few as 50 rows up to 250 rows. Thanks in advance for any help. -- Thanks Steve |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete row 2 cell criteria
just change this:
Set ws = Worksheets("Sheet1") to this: Set ws = ActiveSheet and it will run on the active sheet or this: Set ws = Worksheets(1) if it's always the first sheet -- Gary "Steve Kellogg" wrote in message ... Gary THANK YOU!!!!! My only other question would be can I change "sheet1" to be any sheet I am on? The sheet name is different each day. Again, I can't thank you enough, it worked perfect! -- Thanks Steve "Gary Keramidas" wrote: you can give this a try and see if it meets your needs: Sub dlete_rows() Dim ws As Worksheet Dim i As Long Dim lastrow As Long Set ws = Worksheets("Sheet1") lastrow = ws.Cells(Rows.Count, "D").End(xlUp).Row For i = lastrow To 2 Step -1 With ws.Range("D" & i) If .Value < 96 And .Offset(, 1).Value "" Then .EntireRow.Delete End If End With Next End Sub -- Gary "Steve Kellogg" wrote in message ... I have a spread sheet that changes nightly. In column D every cell has a number. In column E there are either notes or it is blank. What I am looking to do is delete any row, where the number in column D is less than 96 AND there are notes in column E. So if D is higher than 96 and/or E is blank, I want to keep that row, delete all others. On any given day I may have as few as 50 rows up to 250 rows. Thanks in advance for any help. -- Thanks Steve |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete row 2 cell criteria
Works perfect. Thanks again Gary.
-- Thanks Steve "Gary Keramidas" wrote: just change this: Set ws = Worksheets("Sheet1") to this: Set ws = ActiveSheet and it will run on the active sheet or this: Set ws = Worksheets(1) if it's always the first sheet -- Gary "Steve Kellogg" wrote in message ... Gary THANK YOU!!!!! My only other question would be can I change "sheet1" to be any sheet I am on? The sheet name is different each day. Again, I can't thank you enough, it worked perfect! -- Thanks Steve "Gary Keramidas" wrote: you can give this a try and see if it meets your needs: Sub dlete_rows() Dim ws As Worksheet Dim i As Long Dim lastrow As Long Set ws = Worksheets("Sheet1") lastrow = ws.Cells(Rows.Count, "D").End(xlUp).Row For i = lastrow To 2 Step -1 With ws.Range("D" & i) If .Value < 96 And .Offset(, 1).Value "" Then .EntireRow.Delete End If End With Next End Sub -- Gary "Steve Kellogg" wrote in message ... I have a spread sheet that changes nightly. In column D every cell has a number. In column E there are either notes or it is blank. What I am looking to do is delete any row, where the number in column D is less than 96 AND there are notes in column E. So if D is higher than 96 and/or E is blank, I want to keep that row, delete all others. On any given day I may have as few as 50 rows up to 250 rows. Thanks in advance for any help. -- Thanks Steve |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
delete text in a cell using certian criteria | Excel Discussion (Misc queries) | |||
delete row with criteria (vba) | Excel Programming | |||
How to Delete row with criteria ? | Excel Programming | |||
How to Delete row with criteria ? | Excel Programming | |||
How to Delete row with criteria ? | Excel Programming |