Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to clear rows
Thank you all very, very, very much!
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to clear contents and put an X | Excel Discussion (Misc queries) | |||
clear cells macro | Excel Discussion (Misc queries) | |||
macro to clear cells | Excel Discussion (Misc queries) | |||
Clear shhet with macro | Excel Discussion (Misc queries) | |||
Clear Contents Macro | Excel Worksheet Functions |