Hello,
I need help with a macro that will delete rows where 0 is found in certain columns. The range is row 8 to row 1127. The columns that I need the macro to look at are A through E.
Below is a sample data set with a row range of 1 to 50 and a column range of A through E. The "---" are entries in column D.
I need the macro to delete all rows where a 0 appears anywhere in columns A through E. So if done correctly on the sample set below, only 4 rows will remain and all the rest will be deleted.
Also, I don't know if this is relevant, but the 0 values below are not due to 0 being entered into the cell. Rather, the 0 values are there because a cell is linked to another cell which has nothing entered.
Thank you in advance for your help.
I need help with a macro that will delete rows where 0 is found in certain columns. The range is row 8 to row 1127. The columns that I need the macro to look at are A through E.
Below is a sample data set with a row range of 1 to 50 and a column range of A through E. The "---" are entries in column D.
I need the macro to delete all rows where a 0 appears anywhere in columns A through E. So if done correctly on the sample set below, only 4 rows will remain and all the rest will be deleted.
Also, I don't know if this is relevant, but the 0 values below are not due to 0 being entered into the cell. Rather, the 0 values are there because a cell is linked to another cell which has nothing entered.
Thank you in advance for your help.
<table x:str="" style="border-collapse: collapse; width: 272pt;" width="363" border="0" cellpadding="0" cellspacing="0"><col style="width: 56pt;" width="75"> <col style="width: 72pt;" width="96"> <col style="width: 48pt;" span="3" width="64"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; width: 56pt;" width="75" height="17">Decorators</td> <td class="xl24" style="width: 72pt;" width="96">Decorating</td> <td class="xl24" style="width: 48pt;" width="64">Customer</td> <td class="xl24" style="width: 48pt;" x:str="'---" width="64">---</td> <td class="xl25" style="width: 48pt;" x:num="39814" width="64">1/1/2009</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">Decorators</td> <td class="xl24">I&D</td> <td class="xl24">Customer</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">Decorators</td> <td class="xl24">Modular</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">Decorators</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">Decorators</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">Decorators</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" x:num="" height="17">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" x:num="" height="17">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" x:num="" height="17">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" x:num="" height="17">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" x:num="" height="17">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" x:num="" height="17">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" x:num="" height="17">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">Electricians</td> <td class="xl24">Official</td> <td class="xl24">Electrical</td> <td class="xl24" x:str="'---">---</td> <td class="xl25" x:num="39814">1/1/2009</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">Electricians</td> <td class="xl24">Electrical</td> <td class="xl24">Customer</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">Electricians</td> <td class="xl24">Stagehand</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">Electricians</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">Electricians</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">Electricians</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">Electricians</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" x:num="" height="17">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" x:num="" height="17">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" x:num="" height="17">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" x:num="" height="17">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" x:num="" height="17">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" x:num="" height="17">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">Riggers</td> <td class="xl24">Official</td> <td class="xl24">HS</td> <td class="xl24" x:str="'---">---</td> <td class="xl25" x:num="39814">1/1/2009</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">Riggers</td> <td class="xl24">HS</td> <td class="xl24">Customer</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">Riggers</td> <td class="xl24">Elec HS</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">Riggers</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">Riggers</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">Riggers</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" x:num="" height="17">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" x:num="" height="17">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" x:num="" height="17">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" x:num="" height="17">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" x:num="" height="17">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" x:num="" height="17">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">Teamsters</td> <td class="xl24">Official</td> <td class="xl24">Freight</td> <td class="xl24" x:str="'---">---</td> <td class="xl25" x:num="39814">1/1/2009</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">Teamsters</td> <td class="xl24">Freight</td> <td class="xl24">Customer</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">Teamsters</td> <td class="xl24">Small Package</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">Teamsters</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">Teamsters</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">Teamsters</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" x:num="" height="17">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" x:num="" height="17">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" x:num="" height="17">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" x:num="" height="17">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" x:num="" height="17">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" x:num="" height="17">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:num="">0</td> <td class="xl24" x:str="'---">---</td> <td class="xl24" x:num="">0</td> </tr> </tbody></table>
Last edited: