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?

 

[FrenchCelt]

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"]
<tbody>[TR]
[TD]Range("A:A").Find("TOTAL LESS BREAKS").EntireRow.Replace 0, "", xlWhole, , False, , False, False[/TD]
[/TR]
</tbody>[/TABLE]

I got the same error.

 

[Yongle]

Originally Posted by 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

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.

Are there any merged cells in the worksheet?

The obvious question is "Does any cell in column A truly contain the value TOTAL LESS BREAKS?"
It may look like it does , but maybe not. Some tests:

Put this formula in an adjacent cell
=LEN(A12) where A12 is the cell containing TOTAL LESS BREAKS

If it does not return value 17, then problem source is cell value

If it returns the value 17, then try
=LEN(SUBSTITUTE(A12,CHAR(32),""))
- it should return the value 15
If it doesn't then you have an unusual space character

 

[FrenchCelt]

Are there any merged cells in the worksheet?

The obvious question is "Does any cell in column A truly contain the value TOTAL LESS BREAKS?"
It may look like it does , but maybe not. Some tests:

Put this formula in an adjacent cell
=LEN(A12) where A12 is the cell containing TOTAL LESS BREAKS

If it does not return value 17, then problem source is cell value

If it returns the value 17, then try
=LEN(SUBSTITUTE(A12,CHAR(32),""))
- it should return the value 15
If it doesn't then you have an unusual space character

Aha! It returned value 22, so the problem source is cell value. What should I do next? I have some code later in this macro searching for other text in column A from this sheet and it worked flawlessly, but this row is always causing trouble and now I realize why. There are no merged cells, though, as I have code to unmerge the entire worksheet prior to this code.

 

[Rick Rothstein]

Aha! It returned value 22, so the problem source is cell value. What should I do next?

Apparently you have some space characters in the cell along with your text. The best thing would be for you to reenter the text TOTAL LESS BREAKS into the cell... then I would expect the codes we have given you to work.

 

[FrenchCelt]

Apparently you have some space characters in the cell along with your text. The best thing would be for you to reenter the text TOTAL LESS BREAKS into the cell... then I would expect the codes we have given you to work.

Realizing that I needed to fix the text, I tinkered with my code to produce this modification:

Last = Cells(Rows.Count, "A").End(xlUp).Row    For k = Last To 1 Step -1
        If (Cells(k, "A").Value) = "TOTAL" Then
    Cells(k, "A").Select
    ActiveCell.Offset(1, 0).Select
        End If
    Next k
    ActiveCell.FormulaR1C1 = "TOTAL LESS BREAKS"
    Range("A:A").Find("TOTAL LESS BREAKS").EntireRow.Replace 0, "", xlWhole, , , , False, False

This inserted the text I needed in the cell below TOTAL, which has always worked as a reference point, and then the rest of the code worked beautifully to remove the zero as desired.

Thanks to you and Yongle for patiently working with me until I got this sorted out. I really appreciate it!

 

[Rick Rothstein]

This inserted the text I needed in the cell below TOTAL, which has always worked as a reference point, and then the rest of the code worked beautifully to remove the zero as desired.

If the row you want is always one below the row the TOTAL is on, then you can use that cell as the anchor to do what you want no matter what the cell in Column A (one row below the TOTAL cell) says. This single line of code will do what you originally asked for...

[table="width: 500"]
[tr]
	[td]Columns("A").Find("TOTAL", , xlValues, xlWhole, , xlPrevious, False, , False).Offset(1).EntireRow.Replace 0, "", xlWhole, , False, , False, False[/td]
[/tr]
[/table]

 

[FrenchCelt]

If the row you want is always one below the row the TOTAL is on, then you can use that cell as the anchor to do what you want no matter what the cell in Column A (one row below the TOTAL cell) says. This single line of code will do what you originally asked for...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Columns("A").Find("TOTAL", , xlValues, xlWhole, , xlPrevious, False, , False).Offset(1).EntireRow.Replace 0, "", xlWhole, , False, , False, False[/TD]
[/TR]
</tbody>[/TABLE]

I strive for code elegance, so this is appreciated.