Find Zeros in Specific Row and Clear Contents

[FrenchCelt]

Hello, I'm trying to add some VBA code to a macro that requires I find and eliminate any zeroes that appear in a specific row (the row number will vary per spreadsheet). The row will always be labeled TOTAL LESS BREAKS in column A, so I was able to piece together this much to identify that row in my code:

Last = Cells(Rows.Count, "A").End(xlUp).Row    
For h = Last To 1 Step -1
     If (Cells(h, "A").Value) = "TOTAL LESS BREAKS" Then
Cells(h, "A").EntireRow.Select
     End If
Next h

From there, I'm having trouble finishing the code to identify and eliminate any zeros found in that row.

Does anyone have any suggestions?

 

[Yongle]

try this

Sub test()
    Dim cel As Range, celA As Range, colCount As Long
    With ActiveSheet
        Set celA = .Range("A:A").Find("TOTAL LESS BREAKS")
        colCount = .Cells(celA.Row, .Columns.Count).End(xlToLeft).Column
    End With
    
    For Each cel In celA.Resize(, colCount)
        If cel.Value = 0 Then cel.ClearContents
    Next
End Sub

 

[Rick Rothstein]

Here is another macro that you can try...

[table="width: 500"]
[tr]
	[td]Sub Test()
  Range("A:A").Find("TOTAL LESS BREAKS").EntireRow.Replace 0, "", xlWhole, , , , False, False
End Sub[/td]
[/tr]
[/table]

 

[FrenchCelt]

try this

Sub test()
    Dim cel As Range, celA As Range, colCount As Long
    With ActiveSheet
        Set celA = .Range("A:A").Find("TOTAL LESS BREAKS")
        colCount = .Cells(celA.Row, .Columns.Count).End(xlToLeft).Column
    End With
    
    For Each cel In celA.Resize(, colCount)
        If cel.Value = 0 Then cel.ClearContents
    Next
End Sub

I got Run-time error '91':

Object variable or With block variable not set for this line:

colCount = .Cells(celA.Row, .Columns.Count).End(xlToLeft).Column

 

[FrenchCelt]

Here is another macro that you can try...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub Test()
  Range("A:A").Find("TOTAL LESS BREAKS").EntireRow.Replace 0, "", xlWhole, , , , False, False
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

I got the same Run-time error for this as for the code above from Yongle. What's weird is that the first time I put the code in and used F8 to step down to that part, it worked and the zero was deleted, so I backed out and tried to run the macro fully using the shortcut key on a new spreadsheet, and that's when I got the error. And when I went back and used F8 to step back down to that line, I got the error again.

 

[Yongle]

Which sheet were you in when you ran the code?
It looks like VBA is not finding "TOTAL LESS BREAKS"

Try placing both codes (with different names) into the sheet module
(right click on sheet tab \ View Code \ paste in window on right)


My code revised for placing in sheet module

Sub test2()
    Dim cel As Range, celA As Range, colCount As Long
    Set celA = Range("A:A").Find("TOTAL LESS BREAKS")
    colCount = Cells(celA.Row, Columns.Count).End(xlToLeft).Column
    For Each cel In celA.Resize(, colCount)
        If cel.Value = 0 Then cel.ClearContents
    Next
End Sub

 

[FrenchCelt]

Which sheet were you in when you ran the code?
It looks like VBA is not finding "TOTAL LESS BREAKS"

Try placing both codes (with different names) into the sheet module
(right click on sheet tab \ View Code \ paste in window on right)


My code revised for placing in sheet module

Sub test2()
    Dim cel As Range, celA As Range, colCount As Long
    Set celA = Range("A:A").Find("TOTAL LESS BREAKS")
    colCount = Cells(celA.Row, Columns.Count).End(xlToLeft).Column
    For Each cel In celA.Resize(, colCount)
        If cel.Value = 0 Then cel.ClearContents
    Next
End Sub

I tried placing it into the sheet module and got the same result.

I wonder...I ran into a problem with trying to format the immediate cell to the right of the same cell containing TOTAL LESS BREAKS. I have a cell in the row above called TOTAL and using offset I was able to get the cell to the right of that one to format the way I wanted, but when I tried to do the same for TOTAL LESS BREAKS, nothing happened. I got around it by offsetting from TOTAL to go one cell to the right and one cell down to get it formatted how I wanted. I wonder if that kind of issue is affecting this instance.

 

[Yongle]

Please use a NEW worksheet
- enter text TOTAL LESS BREAKS in one of the cells in columns A
- put typical data (including zeros in the same row)
- test both codes again
- both should work for you

After that, focus on the original worksheet to determine what the problem is

 

[Rick Rothstein]

I got the same Run-time error for this as for the code above from Yongle. What's weird is that the first time I put the code in and used F8 to step down to that part, it worked and the zero was deleted, so I backed out and tried to run the macro fully using the shortcut key on a new spreadsheet, and that's when I got the error. And when I went back and used F8 to step back down to that line, I got the error again.

Try changing the line of code in my macro to this and see if that makes it work for you (I added one more False in the middle)...

[table="width: 500"]
[tr]
	[td]Range("A:A").Find("TOTAL LESS BREAKS").EntireRow.Replace 0, "", xlWhole, , False, , False, False[/td]
[/tr]
[/table]

 

[FrenchCelt]

Please use a NEW worksheet
- enter text TOTAL LESS BREAKS in one of the cells in columns A
- put typical data (including zeros in the same row)
- test both codes again
- both should work for you

After that, focus on the original worksheet to determine what the problem is

Ok, I tried that and it worked. Now the question is what about my original worksheet is causing the error. I'll try puzzling it out, but there is no obvious cause.