Writing Macro in Excel 07
 

I sent this before and somehow was not able to retrieve it, so here it is
again:

I need help to develope a macro that will allow me to delete entire rows in
column "B" that have cell values of 0. I would like all other rows with
other cell values and even blank cells in column "B" to remain.

Writing Macro in Excel 07
 

Try the below which works on the active sheet..

Sub DeleteRows()
Dim lngRow as Long
For lngRow = Cells(Rows.Count, "B").End(xlUp).Row To 1 Step -1
If Range("B" & lngRow).Text = "0" Then Rows(lngRow).Delete
Next
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Tony" wrote:

I sent this before and somehow was not able to retrieve it, so here it is
again:

I need help to develope a macro that will allow me to delete entire rows in
column "B" that have cell values of 0. I would like all other rows with
other cell values and even blank cells in column "B" to remain.

Writing Macro in Excel 07
 

Hi,

try the following

Sub DeleteZeroRows()
Dim Bot As Long
Dim Col As Long
Bot = [B65536].End(xlUp).Row
Col = ActiveCell.Column
ActiveCell.EntireColumn.Insert
Range(Cells(1, Col), Cells(Bot, Col)).Select
Selection = "=IF(AND(RC[1]=0,NOT(ISBLANK(RC[1]))),1)"
Selection.SpecialCells(xlCellTypeFormulas, 1).EntireRow.Delete
ActiveCell.EntireColumn.Delete
End Sub

Instead of B65536 whose the column which will contain data down the
farthest. If you have a lot of rows of data this method is faster than doing
a loop through all the cells.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Tony" wrote:

I sent this before and somehow was not able to retrieve it, so here it is
again:

I need help to develope a macro that will allow me to delete entire rows in
column "B" that have cell values of 0. I would like all other rows with
other cell values and even blank cells in column "B" to remain.

Writing Macro in Excel 07
 

Shane Devenshire wrote...
try the following

Sub DeleteZeroRows()
* * Dim Bot As Long
* * Dim Col As Long
* * Bot = [B65536].End(xlUp).Row
* * Col = ActiveCell.Column
* * ActiveCell.EntireColumn.Insert
* * Range(Cells(1, Col), Cells(Bot, Col)).Select
* * Selection = "=IF(AND(RC[1]=0,NOT(ISBLANK(RC[1]))),1)"
* * Selection.SpecialCells(xlCellTypeFormulas, 1).EntireRow.Delete
* * ActiveCell.EntireColumn.Delete
End Sub
....

Not a good idea to insert/delete columns without checking whether
column IV were empty to begin with, and if column IV were empty you
could simply use it for the ancillary formulas then clear it when
done. The formula could then be shortened to

=1/COUNT(RC2)/(RC2=0)

And why are you performing the unnecessary .Select? Too busy to edit
that bit of the macro recorder output?