Delete blank cells containing a formula

[gtd526]

Hello,
I want to delete the formula if the cell has no value.
F21:F29 is the range.
F23:F29 should have the formula removed.
thank you.

The Whole Enchilada.xlsm
	F	G	H
20	Sector	Gain	Stock %
21	Tech 3X	$ (44.72)	-1.87%
22	Finance 3x	$ 119.60	9.01%
23		$ -
24		$ -
25		$ -
26		$ -
27		$ -
28		$ -
29		$ -
Schwab

Cell Formulas
Range	Formula
F21:F29	F21	=IFERROR(INDEX($B:$B,AGGREGATE(15,6,ROW($B$9:$B$15)/ISNA(MATCH($B$9:$B$15,$F$20:$F20,0)),1)),"")
G21:G29	G21	=IFERROR(SUMIFS($K$9:$K$15,$B$9:$B$15,$F21),"")
H21:H29	H21	=IFERROR(AVERAGEIFS($L$9:$L$15,$B$9:$B$15,$F21),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F21:F29	Cell	does not contain a blank value	text	NO

 

[jdellasala]

A quick glance at just the picture of the data above seems to indicate all the cells with a dash ($ -) have a value of zero, not an empty value, so that's the first problem.
Second, a cell with a value of "" is not blank, that is if the formula in cell A1 is ="", the formula ISBLANK(A1) will return false.
You need to use the value you are assigning the cell IFERROR which is "". That doesn't clear the contents of the cell which is required for ISBLANK to return TRUE.
It's great that you pasted the sample using XL2BB, but when it references data outside of the range in question, it's of limited use. Having cells B9 to H29 would have been more helpful. I also always forget to check what the top left cell of a sample is and end up pasting it into cell A1. Starting there couldn't hurt either.

 

[MARK858]

I also always forget to check what the top left cell of a sample is and end up pasting it into cell A1. Starting there couldn't hurt either.

I wouldn't recommend this, you just end up having to amend the ranges if the OP tries adjusting them and gets them wrong.
I'd much rather they posted the actual ranges they are using.

 

[gtd526]

Hello,
I want to delete the formula if the cell has no value.
F21:F29 is the range.
F23:F29 should have the formula removed.
thank you.

The Whole Enchilada.xlsm
	F	G	H
20	Sector	Gain	Stock %
21	Tech 3X	$ (44.72)	-1.87%
22	Finance 3x	$ 119.60	9.01%
23		$ -
24		$ -
25		$ -
26		$ -
27		$ -
28		$ -
29		$ -
Schwab

Cell Formulas
Range	Formula
F21:F29	F21	=IFERROR(INDEX($B:$B,AGGREGATE(15,6,ROW($B$9:$B$15)/ISNA(MATCH($B$9:$B$15,$F$20:$F20,0)),1)),"")
G21:G29	G21	=IFERROR(SUMIFS($K$9:$K$15,$B$9:$B$15,$F21),"")
H21:H29	H21	=IFERROR(AVERAGEIFS($L$9:$L$15,$B$9:$B$15,$F21),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F21:F29	Cell	does not contain a blank value	text	NO

I have a macro that clears the range.

    Dim rng As Range
    
    Workbooks("The Whole Enchilada.xlsm").Worksheets("Schwab").Select

    Set rng = ActiveSheet.Range("F21")

    Dim I As Long
    For I = 1 To 9
        If rng.Cells(I, 1) = "" Then
           rng.Cells(I, 1).ClearContents
        End If
    Next I