Leaving empty cells if output is blank with a macro

BrSuthe

New Member
Joined
Feb 10, 2018
Messages
29
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.


Code:
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
 
Last edited:

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
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.
 
Upvote 0
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?
 
Upvote 0
and is there a sample macro code like this to delete blanks that you can provide, or you can refer me to?
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
I use this macro at a couple of worksheets. One of them has this range: I3:Y13.

Sorry, I corrected the range.
 
Last edited:
Upvote 0
You could use
Code:
Sub BrSuthe()
   Dim Cl As Range
   
   For Each Cl In Range("I3:Y13")
      If Cl.Value = "" Then Cl.ClearContents
   Next Cl
End Sub
 
Upvote 0
Thank you very much! I'll use it, as you said, I realized the cells are not blank in this case.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top