Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete rows
I am trying to delete all rows where the value of one of the cells equals
zero, is there a formula for this? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete rows
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" wrote: I am trying to delete all rows where the value of one of the cells equals zero, is there a formula for this? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete rows
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 On Tue, 29 Jul 2008 11:46:00 -0700, Koz wrote: I am trying to delete all rows where the value of one of the cells equals zero, is there a formula for this? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete rows
"Mike H" wrote: 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" wrote: I am trying to delete all rows where the value of one of the cells equals zero, is there a formula for this? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete rows
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete rows
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 InsertModule. 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 ToolMacroMacros. You can also assign this macro to a button or a shortcut key combo. Gord Dibben MS Excel MVP On Mon, 4 Aug 2008 09:58:02 -0700, Koz wrote: 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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete rows
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! |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete rows
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 ToolsMacroRecord a new MacroStore 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 ToolsAdd-ins. Gord Dibben MS Excel MVP On Tue, 5 Aug 2008 08:42:01 -0700, Koz wrote: 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! |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete rows
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! |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete rows
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 On Tue, 5 Aug 2008 13:09:02 -0700, Koz wrote: 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! |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete rows
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. |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete rows
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. On Tue, 5 Aug 2008 14:09:01 -0700, Koz wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to Delete empty rows in excel in b/w rows with values | Excel Worksheet Functions | |||
Cut filtered rows, paste into next empty row of new sheet, and delete cut rows | Excel Worksheet Functions | |||
delete rows | Excel Discussion (Misc queries) | |||
How to delete rows when List toolbar's "delete" isnt highlighted? | Excel Worksheet Functions | |||
delete empty rows between rows with text | Excel Discussion (Misc queries) |