Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
if function to delete row
hi,
may i know how to write this? if column X or column y or column z is 0 then i need to delete this row. thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
if function to delete row
Right click you sheet tab, view code and paste this in and run it
Sub marine() Dim myrange, copyrange As Range Lastrow = Cells(Cells.Rows.Count, "X").End(xlUp).Row Set myrange = Range("X1:X" & Lastrow) For Each c In myrange If Not IsEmpty(c) And c.Value = 0 And _ Not IsEmpty(c.Offset(, 1)) And c.Offset(, 1).Value = 0 And _ Not IsEmpty(c.Offset(, 2)) And c.Offset(, 2).Value = 0 Then If copyrange Is Nothing Then Set copyrange = c.EntireRow Else Set copyrange = Union(copyrange, c.EntireRow) End If End If Next If Not copyrange Is Nothing Then copyrange.Delete End If End Sub Mike "AskExcel" wrote: hi, may i know how to write this? if column X or column y or column z is 0 then i need to delete this row. thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
if function to delete row
Hi,
thanks but i do not understand your code. may i know if there is any simpler way? thanks "Mike H" wrote: Right click you sheet tab, view code and paste this in and run it Sub marine() Dim myrange, copyrange As Range Lastrow = Cells(Cells.Rows.Count, "X").End(xlUp).Row Set myrange = Range("X1:X" & Lastrow) For Each c In myrange If Not IsEmpty(c) And c.Value = 0 And _ Not IsEmpty(c.Offset(, 1)) And c.Offset(, 1).Value = 0 And _ Not IsEmpty(c.Offset(, 2)) And c.Offset(, 2).Value = 0 Then If copyrange Is Nothing Then Set copyrange = c.EntireRow Else Set copyrange = Union(copyrange, c.EntireRow) End If End If Next If Not copyrange Is Nothing Then copyrange.Delete End If End Sub Mike "AskExcel" wrote: hi, may i know how to write this? if column X or column y or column z is 0 then i need to delete this row. thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
if function to delete row
Hi,
What you can't do is have an IF formula along the lines of =If(x2=0,delete the row, don't delete the row) so if you want to delete rows without code then it's a manual process. You could set up an autofilter to only display the rows that meet your criteria and then delete the visible rows but why not try the code it's simple Right click you sheet tab, view code and paste it in and run it by tapping F5 Mike "AskExcel" wrote: Hi, thanks but i do not understand your code. may i know if there is any simpler way? thanks "Mike H" wrote: Right click you sheet tab, view code and paste this in and run it Sub marine() Dim myrange, copyrange As Range Lastrow = Cells(Cells.Rows.Count, "X").End(xlUp).Row Set myrange = Range("X1:X" & Lastrow) For Each c In myrange If Not IsEmpty(c) And c.Value = 0 And _ Not IsEmpty(c.Offset(, 1)) And c.Offset(, 1).Value = 0 And _ Not IsEmpty(c.Offset(, 2)) And c.Offset(, 2).Value = 0 Then If copyrange Is Nothing Then Set copyrange = c.EntireRow Else Set copyrange = Union(copyrange, c.EntireRow) End If End If Next If Not copyrange Is Nothing Then copyrange.Delete End If End Sub Mike "AskExcel" wrote: hi, may i know how to write this? if column X or column y or column z is 0 then i need to delete this row. thanks |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
if function to delete row
Hi AE,
As far as I am aware, a formula can only give you a result, not an action. So a formula cannot delete rows; you have to use a macro. Regards - Dave. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using a function to delete blank cells | Excel Worksheet Functions | |||
How do I delete a column that has been used for a function? | Excel Worksheet Functions | |||
Delete Cell Function | Excel Worksheet Functions | |||
How do I protect cells against only the "Delete" function? | Excel Discussion (Misc queries) | |||
How do I protect work sheet against the "Delete" function? | Excel Discussion (Misc queries) |