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

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

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

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

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

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