Writing Macro in Excel 07

  • Thread starter Thread starter Tony
  • Start date Start date
T

Tony

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.
 
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
 
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.
 
Shane Devenshire said:
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
Click to expand...
....

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?
 
Back
Top