Delete rows

[Koz]

I am trying to delete all rows where the value of one of the cells equals
zero, is there a formula for this?

 

[Mike H]

Hi,

You didn't say which cell so this deletes the row if the cell in column A is
zero
Right click the sheet tab, view code and paste this in and run it

Sub mersible()
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For x = lastrow To 1 Step -1
If Cells(x, 1).Value = 0 Then
Rows(x).EntireRow.Delete
End If
Next
End Sub

Mike

 

[Gord Dibben]

Formulas cannot delete rows.

Formulas can identify a row with a zero in a cell and you can manuallt
delete those rows.

Perhaps Autofilter may work.

If zeros could be in random cells in random columns across the sheet then AF
would probably not do the job easily.

How about a macro?

Sub delete_zero_rows()
Dim c As Range
With ActiveSheet.Columns("A:G")
Do
Set c = .Find("0", LookIn:=xlValues, lookat:=xlWhole, _
MatchCase:=False)
If c Is Nothing Then Exit Do
c.EntireRow.Delete
Loop
End With
End Sub


Gord Dibben MS Excel MVP

 

[Koz]

Hi,

You didn't say which cell so this deletes the row if the cell in column A is
zero
Right click the sheet tab, view code and paste this in and run it

Sub mersible()
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For x = lastrow To 1 Step -1
If Cells(x, 1).Value = 0 Then
Rows(x).EntireRow.Delete
End If
Next
End Sub

Mike

 

[Koz]

I am trying to delete all rows where the value of one of the cells equals
zero, I have received macros but I have no idea what I am doing! Pasting
them into the sheet causes an error message stating Next without For. I am
using Excel 2000 SR-1 Professional. The cell I am trying to compare to zero
is in row H. The file has been imported from Quickbooks and is supposed to be
used to do a physical inventory.

 

[Gord Dibben]

Sub delete_zero_rows()
Dim c As Range
With ActiveSheet.Columns("H")
Do
Set c = .Find("0", LookIn:=xlValues, lookat:=xlWhole, _
MatchCase:=False)
If c Is Nothing Then Exit Do
c.EntireRow.Delete
Loop
End With
End Sub

If you're not familiar with VBA and macros, see David McRitchie's site for
more on "getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

or Ron de De Bruin's site on where to store macros.

http://www.rondebruin.nl/code.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + r to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run or edit the macro by going to Tool>Macro>Macros.

You can also assign this macro to a button or a shortcut key combo.


Gord Dibben MS Excel MVP

 

[Koz]

The macro worked great the firs time I tried it. I created a new inventory
file from Quickbooks and now the macro does nothing when I run it after I've
copied as instructed?

Thank you very much for helpibng me!

 

[Gord Dibben]

Move the macro to your Personal.xls file then it will be available for all
open workbooks.

If you don't have a Personal.xls yet, with your workbook open, go to
Tools>Macro>Record a new Macro>Store Macro in;>Personal Macro Workbook.

Record yourself copying and pasting something then Stop Recording.

Alt + F11 to open the VBEditor.

You will see the two workbooks. Yours and Personal.xls

Copy the macros/functions from your workbook into the module1 in
Personal.xls.

Note: any hard-coded sheets or books should be changed to ActiveSheet or
ActiveWorkbook

Delete the bogus copy/paste macro if you choose.

Personal.xls can be hidden and saved so's it always opens in the background
with
macros available.

Clear the maros/functions from the original workbook if the above works OK.

An alternative to Personal.xls is to open a new workbook, copy the macro(s)
to a
module in that workbook.

Save As an Add-in which you load through Tools>Add-ins.


Gord Dibben MS Excel MVP

 

[Koz]

I finally determined (duh on my part) that the macro wasn't working because I
hadn't formatted the numbers to remove decimal places. Is there a way to add
0.00 as another variable?

Thanks for all your help, if you need a deal on a mattress let me know!

 

[Gord Dibben]

If all cells are zeros and formatted to 2 DP like 0.00

Change the "0" to "0.00" in the code

But this would miss numbers like 0.00123 that show as 0.00

How about a deal on 120 square meters of good Berber carpetting?


Gord

 

[Koz]

I was thinking of a way to do either 0 or 0.00. As far as the carpeting,
pricing would be from $5-$7.50 per sq. yard.

 

[Gord Dibben]

Sub DeleteRows_2Params()
'Bob Phillips Aug. 26, 2006
Dim iLastRow As Long
Dim I As Long

iLastRow = Cells(Rows.Count, "H").End(xlUp).Row
For I = iLastRow To 1 Step -1
If Cells(I, "H").Value = "0" Or _
Cells(I, "H").Value = "0.00" Then
Rows(I).Delete
End If
Next I

End Sub


Gord...........................no thanks on the carpet.