I'm a bit of a VBA novice, but I know this should be relatively simple.
Example:

A B C D
Bread Butter 15.00 17.00
Bread Cheese 14.00 11.00
Bread Meat ten 13.00
Bread Pickle x y
Bread Sandwich 11.00

I want Excel to delete all rows if there is text or nothing in cell C or
cell D. I want to keep all rows where there is a number in cell C or cell D
of that row. Thus, the first two rows would stay while the last three would
be deleted. I presume I would use the IsNumber function for part of this.

So I'm thinking of using a Do...While loop, but I can't figure out how to
incorporate the "If" statement to look at EITHER cell C OR cell D, or how to
assign the number of rows that the loop will require. Any guidance on this
please? Many thanks!

Sub DeleteStuff()
Const StartRow As Long = 2
Dim StopRow As Long
Dim cnt As Long
With Worksheets("YourSheet")
StopRow = .Range("A" & .Rows.Count).End(xlUp).Row
For cnt = StopRow to StartRow Step -1
If Not IsNumeric(.Range("C" & cnt)) or _
Not IsNumeric(.Range("D" & cnt)) or _
.Range("C" & cnt) = "" or _
.Range("D" & cnt) = "" Then

.Rows(cnt).Delete
End If
Next
End With
End Sub
--
Charles Chickering

"A good example is twice the value of good advice."


"JAnderson" wrote:

I'm a bit of a VBA novice, but I know this should be relatively simple.
Example:

A B C D
Bread Butter 15.00 17.00
Bread Cheese 14.00 11.00
Bread Meat ten 13.00
Bread Pickle x y
Bread Sandwich 11.00

I want Excel to delete all rows if there is text or nothing in cell C or
cell D. I want to keep all rows where there is a number in cell C or cell D
of that row. Thus, the first two rows would stay while the last three would
be deleted. I presume I would use the IsNumber function for part of this.

So I'm thinking of using a Do...While loop, but I can't figure out how to
incorporate the "If" statement to look at EITHER cell C OR cell D, or how to
assign the number of rows that the loop will require. Any guidance on this
please? Many thanks!

Public Sub DeleteRows()
Dim rng As Range
Dim i As Long

Set rng = ActiveSheet.Range("MyData")

For i = rng.Rows.Count To 1 Step -1
If IsNumeric(rng.Cells(i, 3)) And IsNumeric(rng.Cells(i, 4)) Then
If Len(Trim(rng.Cells(i, 3))) 0 And Len(Trim(rng.Cells(i, 4)))
0 Then
' Non-blank, numeric values in both cells. Do not delete.
GoTo NextRow
End If
End If
rng.Cells(i, 1).EntireRow.Delete
NextRow:
Next i

End Sub

Note: using "jump-to" labels is generally frowned upon outside of error
handling. I make an exception to that and also use them (as above) in Loops
as a substitute for the non-existant "GoTo Next" statement. I find the
resulting code easier to read & much easier to maintain. Don't abuse GoTo's.

HTH,


"JAnderson" wrote in message
...
I'm a bit of a VBA novice, but I know this should be relatively simple.
Example:

A B C D
Bread Butter 15.00 17.00
Bread Cheese 14.00 11.00
Bread Meat ten 13.00
Bread Pickle x y
Bread Sandwich 11.00

I want Excel to delete all rows if there is text or nothing in cell C or
cell D. I want to keep all rows where there is a number in cell C or cell
D
of that row. Thus, the first two rows would stay while the last three
would
be deleted. I presume I would use the IsNumber function for part of this.

So I'm thinking of using a Do...While loop, but I can't figure out how to
incorporate the "If" statement to look at EITHER cell C OR cell D, or how
to
assign the number of rows that the loop will require. Any guidance on
this
please? Many thanks!

That works great! Thanks!

"Charles Chickering" wrote:

Sub DeleteStuff()
Const StartRow As Long = 2
Dim StopRow As Long
Dim cnt As Long
With Worksheets("YourSheet")
StopRow = .Range("A" & .Rows.Count).End(xlUp).Row
For cnt = StopRow to StartRow Step -1
If Not IsNumeric(.Range("C" & cnt)) or _
Not IsNumeric(.Range("D" & cnt)) or _
.Range("C" & cnt) = "" or _
.Range("D" & cnt) = "" Then

.Rows(cnt).Delete
End If
Next
End With
End Sub
--
Charles Chickering

"A good example is twice the value of good advice."


"JAnderson" wrote:

I'm a bit of a VBA novice, but I know this should be relatively simple.
Example:

A B C D
Bread Butter 15.00 17.00
Bread Cheese 14.00 11.00
Bread Meat ten 13.00
Bread Pickle x y
Bread Sandwich 11.00

I want Excel to delete all rows if there is text or nothing in cell C or
cell D. I want to keep all rows where there is a number in cell C or cell D
of that row. Thus, the first two rows would stay while the last three would
be deleted. I presume I would use the IsNumber function for part of this.

So I'm thinking of using a Do...While loop, but I can't figure out how to
incorporate the "If" statement to look at EITHER cell C OR cell D, or how to
assign the number of rows that the loop will require. Any guidance on this
please? Many thanks!

Here is another approach

Sub CleanUpData()
Dim rng as range, rng1 as range
On Error resume Next
Range("C:D").Specialcells(xlConstants,xlTextValues ).EntireRow.Delete
Range("C:D").SpecialCells(xlFormulas(xlTextValues) .EntireRow.Delete
Range("C:D").SpecialCells(xlBlanks).entirerow.Dele te
On Error goto 0
End sub

--
Regards,
Tom Ogilvy

"JAnderson" wrote in message
...
That works great! Thanks!

"Charles Chickering" wrote:

Sub DeleteStuff()
Const StartRow As Long = 2
Dim StopRow As Long
Dim cnt As Long
With Worksheets("YourSheet")
StopRow = .Range("A" & .Rows.Count).End(xlUp).Row
For cnt = StopRow to StartRow Step -1
If Not IsNumeric(.Range("C" & cnt)) or _
Not IsNumeric(.Range("D" & cnt)) or _
.Range("C" & cnt) = "" or _
.Range("D" & cnt) = "" Then

.Rows(cnt).Delete
End If
Next
End With
End Sub
--
Charles Chickering

"A good example is twice the value of good advice."


"JAnderson" wrote:

I'm a bit of a VBA novice, but I know this should be relatively simple.
Example:

A B C D
Bread Butter 15.00 17.00
Bread Cheese 14.00 11.00
Bread Meat ten 13.00
Bread Pickle x y
Bread Sandwich 11.00

I want Excel to delete all rows if there is text or nothing in cell C
or
cell D. I want to keep all rows where there is a number in cell C or
cell D
of that row. Thus, the first two rows would stay while the last three
would
be deleted. I presume I would use the IsNumber function for part of
this.

So I'm thinking of using a Do...While loop, but I can't figure out how
to
incorporate the "If" statement to look at EITHER cell C OR cell D, or
how to
assign the number of rows that the loop will require. Any guidance on
this
please? Many thanks!