Do While Problem

B

Bongard

I am trying to figure out how to write a macro that will delete all
information in a row for columns A through O, if the current value of
column c in that row = 350. I would post some of the code I have tried
but it is a huge mess. I feel like this should be pretty simple. If
anyone could help me out it would be much appreciated.

I have been trying to use the do while ...=350 but I can't seem to get
anything to work.


Thanks~
 
B

Bob Phillips

iLastRow = Cells(Rows.Count,"A").End(xlUp).Row

For i = iLastRow To 2 Step -1
If Cells(i,"C").Value = 350 Then
Rows(i).Cells.ClearContents
End If
Next i

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)
 
B

Bongard

Bob thank you for your reply. Is there any way I can do this with a do
while statement? I have to do this about 10 different times in the
spreadsheet so I thought it would be easiest to do one simple do while
statment for each range.

Thanks,
 
B

Bongard

Sub RecalcReportTrial()


Sheets("FASB91RecalcReport").Select
Dim rng As Excel.Range
For Each rng In Worksheets("FASB91RecalcReport").Range("c5:c20")
If rng.Value = 350 Then
rng.ClearContents 'ActiveCell.Offset(0, 2).Select

End If

Next

This is what I am currently trying. The problem is that I can only
delete the rng, and not any other cells. I would also like to use a do
while statment.

Thanks for your help!
 
D

Don Guillett

Use Bob's or data>filter>autofilter on col c for 350>clear. Record a macro
if desired.
 
B

Bongard

I can try and work with Bob's but it clears the contents in more than
columns a through 0. the macro must not delete anything further than
column 0 because I have formulas that must be preserved. How would I
stop Bob's formula from deleting that needed data?

Sub trial()
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row


For i = iLastRow To 2 Step -1
If Cells(i, "C").Value = 350 Then
Rows(i).Cells.ClearContents
End If
Next i


End Sub

Once this is done for the first block I must then use a reference to
get down to the next block of data and do the same thing with the value
501.

Thanks for your help guys,
Brian
 
B

Bob Phillips

Sub trial()

i = 2
Do While Cells(i,"C").Value <> ""
If Cells(i, "C").Value = 350 Then
Cells(i,"C").Resize(,13).ClearContents
End If
i = i + 1
Loop

End Sub

But the other technique can be repeated throughout just as a Do Loop can.
The advantage of a For ... Next loop is that the index is maintained by the
loop.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)
 
B

Bongard

But the other technique can be repeated throughout just as a Do Loop can.
The advantage of a For ... Next loop is that the index is maintained by the
loop.
Click to expand...
Bob thank you for your reply. I pasted the code into VB but when I
tried to run the subroutine nothing happened. I actually got the other
code to work so that will suffice for now. However can you explain what
you meant when you explained the advantage of a ForNext loop? what
index would be maintained? and how do these two funcctions differ?

Thanks guys!

-Brian
 
B

Bongard

But the other technique can be repeated throughout just as a Do Loop can.
The advantage of a For ... Next loop is that the index is maintained by the
loop.
Click to expand...
Bob thank you for your reply. I pasted the code into VB but when I
tried to run the subroutine nothing happened. I actually got the other
code to work so that will suffice for now. However can you explain what
you meant when you explained the advantage of a ForNext loop? what
index would be maintained? and how do these two funcctions differ?

Thanks guys!

-Brian
 
B

Bob Phillips

In your Do ... Loop, you were using the variable i was controlling the loop.
The code has to initialise the index, increment it (i = i + 1), and so on,
but the For i = 1 To Limit .... Next i manages it all for you.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)
 
H

Harlan Grove

Bob Phillips wrote...
Sub trial()

i = 2
Do While Cells(i,"C").Value <> ""
If Cells(i, "C").Value = 350 Then
Cells(i,"C").Resize(,13).ClearContents
End If
i = i + 1
Loop

End Sub
Click to expand...
....

Doesn't this clear cells in columns C to O rather than A to O? OP seems
to want to clear cells in columns A to O. Agreed that this is klunkier
as a Do loop than a For loop.
....
 
Top