Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete row if formula brings back an error
Hi,
I have searched posts but can find nothing that covers this. I originally thought the error brought back may be treated as a value, and could delete the row based on this value but that doesn't seem to work. I have a formula operating in columns J & K on up to 26 sheets. When the formula (in code) reaches the bottom of each sheet it may return the error #VALUE! into J & K. This does not always happen. What I would like to do is search for these #VALUE! cells and delete the rows where they are found. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete row if formula brings back an error
Hi Keri, Try this. Sub test() For Each Sheet In ActiveWorkbook.Sheets For Each cell In Sheet.Columns("J:K").Cells If IsError(cell.Value) = True Then Sheet.Rows(cell.Row).Delete (xlUp) End If Next cell Next Sheet End Sub Regards Ankur Kanchan www.xlmacros.com keri wrote: Hi, I have searched posts but can find nothing that covers this. I originally thought the error brought back may be treated as a value, and could delete the row based on this value but that doesn't seem to work. I have a formula operating in columns J & K on up to 26 sheets. When the formula (in code) reaches the bottom of each sheet it may return the error #VALUE! into J & K. This does not always happen. What I would like to do is search for these #VALUE! cells and delete the rows where they are found. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete row if formula brings back an error
Thankyou for this and putting me on the right track.
ankur wrote: Hi Keri, Try this. Sub test() For Each Sheet In ActiveWorkbook.Sheets For Each cell In Sheet.Columns("J:K").Cells If IsError(cell.Value) = True Then Sheet.Rows(cell.Row).Delete (xlUp) End If Next cell Next Sheet End Sub Regards Ankur Kanchan www.xlmacros.com keri wrote: Hi, I have searched posts but can find nothing that covers this. I originally thought the error brought back may be treated as a value, and could delete the row based on this value but that doesn't seem to work. I have a formula operating in columns J & K on up to 26 sheets. When the formula (in code) reaches the bottom of each sheet it may return the error #VALUE! into J & K. This does not always happen. What I would like to do is search for these #VALUE! cells and delete the rows where they are found. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
get back a deleted column in error in excel, please help | Excel Worksheet Functions | |||
Formula that brings in daily input from a range of cells. | Excel Discussion (Misc queries) | |||
Sum brings back null Value | Excel Discussion (Misc queries) | |||
my sumif statement brings back mutiple answers, how do I fix it? | Excel Worksheet Functions | |||
NEED A FORMULA THAT BRINGS OUT NAME OF COLUMN (HEADER) A VALUE BE. | Excel Worksheet Functions |