Deleting cells based on contents or displayed contents

[thebottomline]

Hi there,

I have a spreadsheet that I'll be passing on to other people who know nothing of excel, so I need to simplify it as much as possible, so I'm writing Macros to make it a simple button-press to formulate reports. I need to apply a formula to a column but it is of variable length, so what my plan to do is apply the fomula using an if function, where it would return a specific value if it reached the end of the table. For example, in column E i input the formula:

=IF(ISBLANK(D56),"purple",SUMIFS('Raw Data'!H:H,'Raw Data'!C:C,Information!B56,'Raw Data'!A:A,Information!A56))

I did this with the plan of doing a find and replace, finding "purple" and replacing it with nothing, hoping that would delete the cell contents, but it simply removed the word purple from the formula. Is there any way I can make it so I can include a macro that adds a variable to a field of variable length, or, more simply, delete cells based on criteria?

Thanks for any help.

[JBeaucaire]

If you use REPLACE, search for *purple* and replace with nothing. When using the REPLACE wizard, don't ignore the OPTIONS.

Are you wanting to delete cell contents or delete rows from your data? Utilizing VBA, there are many ways to delete empty rows quite fast. Is the column to check for blanks column D? Then something like this:

Please Login or Register  to view this content.

To do the same thing with just cell contents:

Please Login or Register  to view this content.

[thebottomline]

Thank you very much indeed, I'll try both of those out. I like to learn the detail behind coding and functions, so I can use them again, would I be able to find that out via google?

[JBeaucaire]

Is there much you can't learn by Googling nowadays? I Google to great success. Just form your Google search properly:

excel vba delete blank rows
excel sumproduct function

...etc.

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].

(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated. It is found across from the "time" in each of our posts.)