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

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

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