One more Macro question please

Terrie

New Member
Joined
Jun 10, 2004
Messages
33
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

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
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

MrExcel MVP
Joined
May 28, 2004
Messages
4,237
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
Joined
Jun 10, 2004
Messages
33
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
Joined
May 28, 2004
Messages
4,237

ADVERTISEMENT

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
Joined
Jun 10, 2004
Messages
33
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
Joined
May 28, 2004
Messages
4,237
I think...hm. Not sure. Can you post the code you're using, please? Copy it straight from the VBE.
 

Terrie

New Member
Joined
Jun 10, 2004
Messages
33
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 !
 

Forum statistics

Threads
1,148,055
Messages
5,744,544
Members
423,882
Latest member
Seeham

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top