Leaving empty cells if output is blank with a macro

[BrSuthe]

I have the following macro. Everything is ok with it, I just need to leave empty cell if there is no output (no values/numbers). Is it possible to do this? Can I get some help, please? Thanks.

In the current code, if there is an output in the cell (eg 6), thats ok. but if there is no output, then I see a visibly blank cell with udf formula in it. but since it's not empty, my other formulas depending on this output doesn't give results correctly.

Option Explicit
Function MFx(r As Range, s As String, Optional delimiter As String = ",") As String

Dim c As Range
Dim result As String

For Each c In r
    If InStr(delimiter & s & delimiter, delimiter & c & delimiter) <> 0 Then result = result & c & delimiter
Next c

If Len(result) > 0 Then result = Left(result, Len(result) - Len(delimiter))

MFx = result

End Function

 

[Fluff]

As far as I know UDF cannot delete itself.
So you will either need to delete it manually, write a macro to remove it, or modify your other formulae.

 

[BrSuthe]

thanks, Fluff. Is it possible to add another macro at the end of this one, so when the first one is done, second one executes and makes the deletions?

 

[BrSuthe]

and is there a sample macro code like this to delete blanks that you can provide, or you can refer me to?

 

[Fluff]

thanks, Fluff. Is it possible to add another macro at the end of this one, so when the first one is done, second one executes and makes the deletions?

What you have supplied is a UDF, which is not quite the same as a macro and, no you cannot add anything to it, to delete itself.

 

[Fluff]

and is there a sample macro code like this to delete blanks that you can provide, or you can refer me to?

Because the cells contain a formula calling the the UDF they are not blank. You would need to know exactly where you are using the UDF & point another macro at those cells.
What is the range of cells that call the UDF?

 

[BrSuthe]

I use this macro at a couple of worksheets. One of them has this range: I3:Y13.

Sorry, I corrected the range.

 

[Fluff]

You could use

Sub BrSuthe()
   Dim Cl As Range
   
   For Each Cl In Range("I3:Y13")
      If Cl.Value = "" Then Cl.ClearContents
   Next Cl
End Sub

 

[BrSuthe]

Thank you very much! I'll use it, as you said, I realized the cells are not blank in this case.

 

[Fluff]

You're welcome