# 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

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

<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>

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``````

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 !

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] ?

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.

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

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
1
Views
292
Replies
1
Views
218
Replies
6
Views
292
Replies
7
Views
316
Replies
2
Views
179

1,221,186
Messages
6,158,413
Members
451,492
Latest member
ichinisan123

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