# One more Macro question please

#### Terrie

##### New Member
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

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

#### Smitty

##### Legend

<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

##### MrExcel MVP
You could code the formulas in too (mostly for future refernce):
Code:
``````For i = 1 To Range("C65536").End(xlUp).Row
Range("AB" & i).FormulaR1C1 = "=countblank(RC[-25]:RC[-1])"
Next i``````

#### Terrie

##### New Member
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

##### MrExcel MVP

Maybe you'd have to go from the bottom to the top, instead? Switch these around?:
Code:
``([A1], [A65536].End(xlUp)``
to
[A65536].End(xlUp), [A1] ?

#### Terrie

##### New Member
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

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

#### Terrie

##### New Member
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 !

Replies
3
Views
141
Replies
32
Views
507
Replies
1
Views
159
Replies
1
Views
318
Replies
5
Views
256

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,151,971
Messages
5,767,389
Members
425,410
Latest member
SmittyT

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back