Help with my "clear contents" code

[thewiseguy]

Hi everyone,

Here is my code:

Sub ClearZeroRowS2()
Application.ScreenUpdating = False
With Sheets("PRF1").Range("Q7:Q319").Resize(, Columns.Count - 16)
.AutoFilter Field:=1, Criteria1:="=0"
On Error Resume Next
.Offset(1).Resize(313).SpecialCells(xlCellTypeVisible).ClearContents
On Error GoTo 0
.AutoFilter
End With
Application.ScreenUpdating = True
End Sub


I am trying to have my code look in range Q7:Q319 and wherever there is a "0" value to clear the contents of the entire row. Any idea why it's not working??

TIA!

 

[mooseman]

Comment out your error checking and step throught it to see what it does.

BTW - What is the "On Error GoTo 0" doing?

 

[Colin Legg]

Try

Sub ClearZeroRowS2()
    Dim rngToClear As Range
    
    With Sheets("PRF1")
        .AutoFilterMode = False
        
        With .Range("Q7:Q319")
            .AutoFilter Field:=1, Criteria1:="=0"
        
            On Error Resume Next
            'assumes there is a header row
            Set rngToClear = .Offset(1).Resize(.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible)
            On Error GoTo 0
            
            If Not rngToClear Is Nothing Then rngToClear.EntireRow.ClearContents
            .AutoFilter
        End With
    End With
End Sub

Hope that helps...

 

[thewiseguy]

Try

Sub ClearZeroRowS2()
    Dim rngToClear As Range
    
    With Sheets("PRF1")
        .AutoFilterMode = False
        
        With .Range("Q7:Q319")
            .AutoFilter Field:=1, Criteria1:="=0"
        
            On Error Resume Next
            'assumes there is a header row
            Set rngToClear = .Offset(1).Resize(.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible)
            On Error GoTo 0
            
            If Not rngToClear Is Nothing Then rngToClear.EntireRow.ClearContents
            .AutoFilter
        End With
    End With
End Sub

Hope that helps...

hi,

i tried this. it runs but then nothing changes. any ideas?

 

[Colin Legg]

Hi,

Step through the code one line at a time using the F8 key.

Does it filter out all of your zero values in column Q?

If you add this line before the "On Error Goto 0" line.

debug.print rngToClear.Address

What is printed in the immediate window? Some cell addresses? Nothing?
Does it tally with the cells that you think contain zeros?

Hope that helps

 

[thewiseguy]

Hi,

Step through the code one line at a time using the F8 key.

Does it filter out all of your zero values in column Q?

If you add this line before the "On Error Goto 0" line.

debug.print rngToClear.Address

What is printed in the immediate window? Some cell addresses? Nothing?
Does it tally with the cells that you think contain zeros?

Hope that helps

not really sure how to step through the code as someone else gave this to me.
i added what you suggested but the same thing is happening.

 

[Colin Legg]

To step through the code, left click in the procedure, so that the cursor is flashing anywhere inside the procedure.

Then just start pressing F8. Each line of code will be executed when you press F8. You can watch the excel spreadsheet as you do this to see what happens.

My initial suspicion is that, if nothing is happening, then the range Q7:Q319 on that sheet does not contain any cells which have truly zero values.

 

[thewiseguy]

To step through the code, left click in the procedure, so that the cursor is flashing anywhere inside the procedure.

Then just start pressing F8. Each line of code will be executed when you press F8. You can watch the excel spreadsheet as you do this to see what happens.

My initial suspicion is that, if nothing is happening, then the range Q7:Q319 on that sheet does not contain any cells which have truly zero values.

your last point may be on. in my "Q" range, there is actually a formula:

=MIN(O9,P9)

will this affect the code?

 

[Colin Legg]

What values are in O9 and P9?

When I say that, I mean what is in the formula bar for each of those cells, not what is actually shown on the worksheet itself.

 

[thewiseguy]

What values are in O9 and P9?

When I say that, I mean what is in the formula bar for each of those cells, not what is actually shown on the worksheet itself.

09: =IF(ISERROR(B9*N9),"",B9*N9)
P9: =IF(ISERROR(J9*30),"",J9*30)

B9: =INPUT!C7
N9: =IF(ISERROR(MAX(F9-M9,0)),"",MAX(F9-M9,0))

J9: =INPUT!I7

INPUT!C7 = 200 (this cell will always be a different number, entered manually)

F9: =IF(ISERROR(E9/B9),"",E9/B9)
M9: =IF(ISERROR(L9/B9),"",L9/B9)

INPUT!I7 = 1 (this cell will alays be a different number, entered manually)


sorry hope this is what you were looking for!