Deleting Rows
 

Hello, I need to create a macro to delete an entire row if a cell
value in a selected cell of that row has a zero value. Any
suggestions?

Deleting Rows
 

Try something like

Sub AAA()
If IsEmpty(ActiveCell.Value) = False Then
If IsNumeric(ActiveCell.Value) = True Then
If ActiveCell.Value = 0 Then
ActiveCell.EntireRow.Delete xlShiftUp
End If
End If
End If
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)


"amanda" wrote in message
ups.com...
Hello, I need to create a macro to delete an entire row if a cell
value in a selected cell of that row has a zero value. Any
suggestions?

Deleting Rows
 

Try searching this NG. But if you're too lazy to do that start with this:
Sub DeleteRows()
Const StartRow As Long = 1 'Row to Start looking at
Const StopRow As Long = 65000 'Row to Stop looking at
Const Col As Long = 2 'Column to search for 0 in
Dim cnt As Long
For cnt = StopRow to StartRow Step -1
If Cells(cnt,Col) = 0 Then Rows(cnt).Delete
Next
End Sub
--
Charles Chickering

"A good example is twice the value of good advice."


"amanda" wrote:

Hello, I need to create a macro to delete an entire row if a cell
value in a selected cell of that row has a zero value. Any
suggestions?

Deleting Rows
 

Hi Charles,
I would give the macro a much more descriptive name
of what rows are being deleted. Simply going through
65000 rows is not going to be efficient, nor is it correct.
While your version may have 65536 rows that restriction
will be upped considerably in Excel 2007, and I presume
nobody is still using Excel 95. But going through 65536
cell comparisons on my computer would take a few minutes,
if you have only 300 rows, it should go extremely fast even
on my computer when it had 128MB RAM.

col = activecell.column
StopRow = Cells(Rows.Count, col).End(xlUp)).Row

Changed it from Column B to the column of the activecell
to make the macro more generic.

Sub Del_rows_with_zero_in_column_of_activecell()
Const StartRow As Long = 1 'Row to Start looking at
Dim StopRow As Long
Dim Col As Long
Col = ActiveCell.Column
StopRow = Cells(Rows.Count, Col).End(xlUp).Row
Dim cnt As Long
For cnt = StopRow To StartRow Step -1
If IsNumeric(Cells(cnt, Col)) Then
If Cells(cnt, Col) = 0 Then Rows(cnt).Delete
End If
Next
End Sub

Additional references: (the first is on Slow Response)
http://www.mvps.org/dmcritchie/excel/slowresponse.htm
http://www.mvps.org/dmcritchie/excel/delempty.htm
http://www.mvps.org/dmcritchie/excel/toolbars.htm

---
HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm

"Charles Chickering" wrote in message
...
Try searching this NG. But if you're too lazy to do that start with this:
Sub DeleteRows()
Const StartRow As Long = 1 'Row to Start looking at
Const StopRow As Long = 65000 'Row to Stop looking at
Const Col As Long = 2 'Column to search for 0 in
Dim cnt As Long
For cnt = StopRow to StartRow Step -1
If Cells(cnt,Col) = 0 Then Rows(cnt).Delete
Next
End Sub
--
Charles Chickering

"A good example is twice the value of good advice."


"amanda" wrote:

Hello, I need to create a macro to delete an entire row if a cell
value in a selected cell of that row has a zero value. Any
suggestions?

Deleting Rows
 

correction must check the cell that it is not empty as well
like Chip indicated in his reply.

Sub Del_rows_with_zero_in_column_of_activecell()
Const StartRow As Long = 1 'Row to Start looking at
Dim StopRow As Long
Dim Col As Long
Col = ActiveCell.Column
StopRow = Cells(Rows.Count, Col).End(xlUp).Row
Dim cnt As Long
For cnt = StopRow To StartRow Step -1
If Not IsEmpty(Cells(cnt, Col)) Then
If IsNumeric(Cells(cnt, Col)) Then
If Cells(cnt, Col) = 0 Then Rows(cnt).Delete
End If
End If
Next cnt
End Sub