macro to clear rows
 

Hi, I have a most likely easy question that is absolutely
stumping me.

Let's say I have two columns of data. Whenever the data
in columnA is zero, I want to delete (clear contents) that
entire row of data. Thus, if cell A40 was zero, B40 would
be deleted as well.

I don't even care of the rest of the data is shifted up
with a delete. My problem is that when I run the macro,
it misses a bunch of the zeros that it was supposed to
detect. Any help?

Here's what I'm working with:



Sub delete()

For rwIndex = 1 To 200
With ActiveSheet.Cells(rwIndex, 1)
If ((.Value) = 0) Then
Rows(rwIndex).delete
End If
End With
Next rwIndex

End Sub

macro to clear rows
 

you need to reverse the loop. a for-next your way 'skips' a row when
deleting
think of it, let us suppose you test A400 which is zero. delete row 400, so
what was 401 becomes 400 and the 'next' part of the loop increments the
counter to 401. thus you simply misses what was 401.

Sub delete()
With ActiveSheet
For rwIndex = 200 to 1 STEP -1
If .Cells(rwIndex, 1).Value = 0 Then
Rows(rwIndex).delete
End If
Next rwIndex
End With
End Sub

Note that I moved the WITH statement outside the loo too...
--
Patrick Molloy
Microsoft Excel MVP
----------------------------------
"Peter H" wrote in message
...
Hi, I have a most likely easy question that is absolutely
stumping me.

Let's say I have two columns of data. Whenever the data
in columnA is zero, I want to delete (clear contents) that
entire row of data. Thus, if cell A40 was zero, B40 would
be deleted as well.

I don't even care of the rest of the data is shifted up
with a delete. My problem is that when I run the macro,
it misses a bunch of the zeros that it was supposed to
detect. Any help?

Here's what I'm working with:



Sub delete()

For rwIndex = 1 To 200
With ActiveSheet.Cells(rwIndex, 1)
If ((.Value) = 0) Then
Rows(rwIndex).delete
End If
End With
Next rwIndex

End Sub

macro to clear rows
 

Try using For rwIndex = 200 To 1 Step -1

"Peter H" wrote in message
...
Hi, I have a most likely easy question that is absolutely
stumping me.

Let's say I have two columns of data. Whenever the data
in columnA is zero, I want to delete (clear contents) that
entire row of data. Thus, if cell A40 was zero, B40 would
be deleted as well.

I don't even care of the rest of the data is shifted up
with a delete. My problem is that when I run the macro,
it misses a bunch of the zeros that it was supposed to
detect. Any help?

Here's what I'm working with:



Sub delete()

For rwIndex = 1 To 200
With ActiveSheet.Cells(rwIndex, 1)
If ((.Value) = 0) Then
Rows(rwIndex).delete
End If
End With
Next rwIndex

End Sub

macro to clear rows
 

Sub DeleteZeroAndBlankRows()

With Range("A1:A200")
.Replace _
What:="0", _
Replacement:="", _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False

.SpecialCells(xlBlanks).EntireRow.Delete xlUp
End With

End Sub

Bob Kilmer

"Peter H" wrote in message
...
Hi, I have a most likely easy question that is absolutely
stumping me.

Let's say I have two columns of data. Whenever the data
in columnA is zero, I want to delete (clear contents) that
entire row of data. Thus, if cell A40 was zero, B40 would
be deleted as well.

I don't even care of the rest of the data is shifted up
with a delete. My problem is that when I run the macro,
it misses a bunch of the zeros that it was supposed to
detect. Any help?

Here's what I'm working with:



Sub delete()

For rwIndex = 1 To 200
With ActiveSheet.Cells(rwIndex, 1)
If ((.Value) = 0) Then
Rows(rwIndex).delete
End If
End With
Next rwIndex

End Sub

macro to clear rows
 

I've run into this before. The reason it's missing items is because
when it deletes the row, the row numbers shift up and your counter,
rowIndex, misses one. If you want to delete, use a Do/While loop. If
clearing is okay, change .delete to .clear.

Sub delete()

rwIndex = 1
Do while rwIndex <= 200
With ActiveSheet.Cells(rwIndex, 1)
If ((.Value) = 0) Then
Rows(rwIndex).delete
Else
rwIndex = rwIndex + 1
End If
End With
Loop

End Sub

Sub delete()

For rwIndex = 1 To 200
With ActiveSheet.Cells(rwIndex, 1)
If ((.Value) = 0) Then
Rows(rwIndex).clear
End If
End With
Next rwIndex

End Sub

Let me know if you have questions.

Mark

---
Mark Bigelow
mjbigelow at hotmail dot com
http://hm.imperialoiltx.com

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

macro to clear rows
 

Thank you all very, very, very much!

macro to clear rows
 

I dont know if the cells in those rows has to meet a criteria if so then:

dim cell as range

for each cell in range ("your range here")

if cell.value = "whatever" then

cell.entirerow.clearcontents

end if
next cell


if you just want to clear rows then

sub clear ()
range("your ranger here").clearcontents


end sub















"Peter" wrote in message
...
Thank you all very, very, very much!