Loop issue when cell value is null

**starcraftbud** · 10-06-2009, 04:52 PM

Hey,

I'm trying to run a loop which will do something when there is a value in the cell, but skip the cell (or delete the row) when there is no value.

I pulled this from another thread, but it isn't working for me:

Please Login or Register  to view this content.

I think it might be because my blank cells have a formula, so they aren't really blank? I have a formula like this: =IF(M2="Yes",I2,"")

Here is the part of code I am trying to loop through in case my above description is not enough information (only the beggining is giving me trouble, and the red section could change depending if I delete the rows or skip them):

Please Login or Register  to view this content.

**JBeaucaire** · 10-06-2009, 05:23 PM

The trick to using that would be for your formula to return different TYPES of answers. In your formula: =IF(M2="Yes",I2,"")
...what kind of value is I2? If it's text, then don't use "" as the other answer, use zero. This will give you numerics and text in opposite parts of the formula.

Then you can use the SpecialCells feature to search for the "Type" of cell values to delete.

To search for and delete the numerics or text values:

Please Login or Register  to view this content.

**starcraftbud** · 10-07-2009, 09:29 AM

Cells in column I are numeric values.

If I change the formulas in column A to be =IF(M2="Yes",I2,"x")

can I somehow delete rows with the value "x"?

Thanks for the help so far. I learned a little from your response.

**JBeaucaire** · 10-07-2009, 09:36 AM

The second line of code provided above would spot all the formulas with a TEXT result, that would be all your "x" values, and delete those rows. That's teh fastest way to do that.

If there are text values elsewhere in the column you DON'T want deleted, then restrict the range more precisely. I've used the entire column as a the range, but you could change that to a short range:

Please Login or Register  to view this content.

**starcraftbud** · 10-07-2009, 09:46 AM

Sorry JBeau, I only saw your second post after posting this. I'll take a look at that first. Thank you for the quick response.

Or another route I am trying is to paste special values. This should delete all the blank cells, but it is still not working. I currently have this:

Please Login or Register  to view this content.

I don't get any debug errors, but the rows just don't get deleted. There is also no values in the cells, so I cannot figure out why the row is not deleting. Anyone have any suggestions?

**JBeaucaire** · 10-07-2009, 09:49 AM

Since you've been given the answer for your specific question and it's not working for you, there must be something not obvious interfering with the process. Rather than doing this back and forth, just post up a sample workbook with actual example data that reflects the need. BEFORE/AFTER sheets if necessary to make it clear.

By looking directly at your data, we can surmise why you're having problems with the suggestions given, or perhaps spot an easier way to accomplish the goal not evident without seeing your sheet.

Click GO ADVANCED and use the paperclip icon to post up your workbook.

**starcraftbud** · 10-07-2009, 10:09 AM

I'm all set. It worked. Thanks for the help. It was unclear from your first post which line of code referred to text. Once that was cleared up in your second post, it worked. Nice job and thanks again for the help.