Blank Cells

Mar 8th, 2002, 08:33 AM

I am using the IF worksheet function. If the logical test produces a value of 0, I'd like the value_if_true to be a blank cell so I could go back later and do an Edit, GoTo, Special, Blanks and delete the rows that contain blanks. I've tried making the value_if_true to be "" and that makes the cell appear blank, but the GoTo procedure described above doesn't recognize the cell as blank. Any ideas on what value_if_true Excel would recognize as a true blank cell?

Mark W. · Mar 8th, 2002, 08:36 AM

Can't be done using an Excel worksheet function. All functions MUST return something.

Derek · Mar 8th, 2002, 09:44 AM

Hi
If you are going to delete the records anyway, try having your formula return "^" instead of zero or "". If ^ is a character that you don't normally use you can then change them to blank cells via the Replace function (ie in the find-what box put ^ and put nothing at all in the replace-with-what box). You can then use Edit, GoTo, Blanks the way you wanted to
Just a thought
regards
Derek
ps ^ is the character above the 6 on your keyboard

[ This Message was edited by: Derek on 2002-03-08 08:47 ]

Mark W. · Mar 8th, 2002, 09:54 AM

Quote:

If you are going to delete the records anyway...

Why not just return #N/A and use Edit | Go To... Special... Formulas (and leave only the Errors box checked)?

[ This Message was edited by: Mark W. on 2002-03-08 08:54 ]

Mar 8th, 2002, 11:51 AM

Thank you for the replies - returning a unique character then replacing it with a blank or returning an error value and doing a GoTo error both accomplish my goal - thanks!

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 8th, 2002, 08:33 AM	#1
Guest Posts: n/a	I am using the IF worksheet function. If the logical test produces a value of 0, I'd like the value_if_true to be a blank cell so I could go back later and do an Edit, GoTo, Special, Blanks and delete the rows that contain blanks. I've tried making the value_if_true to be "" and that makes the cell appear blank, but the GoTo procedure described above doesn't recognize the cell as blank. Any ideas on what value_if_true Excel would recognize as a true blank cell?

Mar 8th, 2002, 08:36 AM	#2
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	Can't be done using an Excel worksheet function. All functions MUST return something.

Mar 8th, 2002, 09:44 AM	#3
Derek Board Regular Join Date: Feb 2002 Location: Perth Australia Posts: 1,567	Hi If you are going to delete the records anyway, try having your formula return "^" instead of zero or "". If ^ is a character that you don't normally use you can then change them to blank cells via the Replace function (ie in the find-what box put ^ and put nothing at all in the replace-with-what box). You can then use Edit, GoTo, Blanks the way you wanted to Just a thought regards Derek ps ^ is the character above the 6 on your keyboard [ This Message was edited by: Derek on 2002-03-08 08:47 ]

Mar 8th, 2002, 11:51 AM	#5
Guest Posts: n/a	Thank you for the replies - returning a unique character then replacing it with a blank or returning an error value and doing a GoTo error both accomplish my goal - thanks!