is code available to do the equivalent of hitting the "delete" key...

[sadams1]

I have an array that is a mix of cells with printing & non printing characters. I'd like the cells that appear blank or empty to be returned to the equivalent of hitting the "delete" key. I've used the "clean", "trim" functions but the cells still seem to have something remaining. Manually hitting the delete key solves the problem but I'd like to see if code could be used to do the same.

 

[Joe4]

Have you tried "ClearContents", i.e.

Range("A1").ClearContents

 

[sadams1]

hello & thank you for the reply! Just as a head's up, I'm a total hack so I'll mess around with stuff I read here or online & occasionally get lucky & something will work. In this case, what I've messing with is something that looks like this...

=IF(A1="x", <do nothing>, <ClearContents>)


which obviously just laughs at me.

Ideally, a range would be selected then each cell is checked for an "x" & if "x" is present, leave it alone but if not "x" then do the same as hitting the delete key.

 

[Joe4]

You cannot use an Excel formula to clear a cell. Excel formulas can only return values to the cell that they exist in.
So, if an cell had a formula, it obviously could not be blank at the same time!

What you want to do would require VBA (which is what I thought you were asking when you put the word "code" in your title).
The line of code that I gave above is VBA code. To apply this to all cells in a selection, you would run code that looks something like this:

Sub MyClearCode()

    Dim cell As Range
    
    For Each cell In Selection
        If cell.Value = "X" Then cell.ClearContents
    Next cell
    
End Sub

 

[sadams1]

PERFECT...this is exactly what I was doing but couldn't figure out how to make your code work in VBA. I've got a bunch of simple things like this with the help of others that may be useful to others but don't think there's a "library" in this board where they could be posted. Regardless, thank you very much & am amazed how simple the solution was.!

 

[Joe4]

You are welcome.
Glad I was able to help!

 

[sadams1]

Sorry to bother but looks like the operator for "does not equal" x" should be "<>" so I changed it to...


If cell.Value <> "X" Then cell.ClearContents


now it eats the cells that have "x"

If put into words, I'd like the original code to read like this...

If cell.Value = "X" Then LEAVE ALONE otherwise cell.ClearContents

not sure if this makes sense?

 

[Joe4]

I think the issue you are now experiencing is the "X" is not equal to "x", and it sounds like you want to leave alone BOTH "X" and "x", right?

If so, try this:
If UCase(cell.Value) <> "X" Then cell.ClearContents

 

[sadams1]

BOOM! That's it! Didn't even think about the upper/lower case thing...my apologies. Many thanks again!

 

[Joe4]

You are welcome.

No worries.
Sorry I had the condition flipped/reversed originally!