VBA help- Do While and If statements
 

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!

VBA help- Do While and If statements
 

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!

VBA help- Do While and If statements
 

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!

VBA help- Do While and If statements
 

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!

VBA help- Do While and If statements
 

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!