One more Macro question please

[Terrie]

I am almost where I need to be on this project. Just one more hold up.

At the end of each row there is a formula that adds the number of blank spaces in a range of 25 cells. If the end result of the formula has a total of 25, that means that the entire range is blank.
=countblank(C1:AA1)

What I want to do is delete all of the rows where the total = 25.

1 25
2 20
3 10
4 25
5 15
6 25

I want to delete row 1, 4 and 6.

Am having a hard time writing the macro or formula.
Can I make a formula that says if A15 = 25 then delete A15? or add to the existing formula?
=countblank(C1:AA1) if 25 delete row

Any help would be greatly appreciated!
Thanks

 

[Smitty]

How about:

<font face=Tahoma><SPAN style="color:#00007F">Sub</SPAN> Delete25()
    <SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> Range
        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> Range([A1], [A65536].End(xlUp))
            <SPAN style="color:#00007F">If</SPAN> c.Value = 25 <SPAN style="color:#00007F">Then</SPAN> c.EntireRow.Delete
        <SPAN style="color:#00007F">Next</SPAN> c
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[Tazguy37]

You could code the formulas in too (mostly for future refernce):

For i = 1 To Range("C65536").End(xlUp).Row
    Range("AB" & i).FormulaR1C1 = "=countblank(RC[-25]:RC[-1])"
Next i

 

[Terrie]

Thanks for your quick response !

I tried what you gave me. I copied/pasted into my module. I did change A1 A6536 to AC1 and AC6536 because AC is the cell that has the formula.

However, when I ran the macro, only some of the rows with "25" were deleted, but not all of them. Not really sure what went wrong. Any suggestions?

Thanks !

 

[Tazguy37]

Maybe you'd have to go from the bottom to the top, instead? Switch these around?:

([A1], [A65536].End(xlUp)

to
[A65536].End(xlUp), [A1] ?

 

[Terrie]

So ... reversing it didn't work either. I would try to other suggestion you gave, but I know very little about what these codes and directions mean and I can't understand what the "for i =1 To Range .... means. Sorry.

Is it something that can be explained in a simple language for us simple folks?

Thanks again.

 

[Tazguy37]

I think...hm. Not sure. Can you post the code you're using, please? Copy it straight from the VBE.

 

[Terrie]

I cannot copy/paste the spreadsheet because my Company's protection does not let me. The code I entered is what you gave me yesterday.

Sub Delete25()
Dim c As Range
For Each c In Range([CA1], [CA65536].End(xlUp))
If c.Value = 25 Then c.EntireRow.Delete
Next c
End Sub

It deleted some of the rows with 25, but not all of them.

Thanks !