Excel: Center across selection with cells containing nothing (as in "") but formulas

Question

I am building a to do list in Excel. The user can fill up to 10 lines per KPI for multiple KPIs. The width is fixed to 8 columns. Afterwards, I extract that same list without empty lines into another sheet (w/o VBA). And then I export it to ppt as an OLE Object via VBA. Now the Problem is: The KPI title cannot be read because it is cut off when the cell ends even though the next cell is visibly empty (Only a Formula is in there which enters "" for empty cells) and center across selection is activated.

I somehow need to make that KPI name visible (preferably without VBA).

Note: Adding it in front of each line is not feasible due to width limitations imposed through the slides' size. Also Copy and then paste special of the OLE Data did not work. Input

Selection without empty rows. Problem is Cell A3 for example

If there's any content in the cell to the right then the text will be cut off: I don't think there's any way to make it flow over that cell. What's the purpose of the "no empty lines" sheet - is it just an intermediate step before PPT? — Tim Williams
So to be clear, there's a formula in B3 that's returning ""? — BruceWayne
Have you looked at merging the cells for the KPI name, to ensure that there is enough space? My assumption is that the key points indicator title is above the 10 lines, so you have at least 8 columns to merge for housing the KPI name. — Cyril
@BruceWayne: The Cell next to it has the following code: <pre><Code> =IF(IFERROR(INDEX(Input!E$6:E$171;MATCH(ROW()-ROW($A$2);Input!$C$6:$C$171;0));"")=0;"";IFERROR(INDEX(Input!E$6:E$171;MATCH(ROW()-ROW($A$2);Input!$C$6:$C$171;0));"")) </br> But you can basically omit everything but <pre><Code> =IFERROR(INDEX(Input!E$6:E$171;MATCH(ROW()-ROW($A$2);Input!$C$6:$C$171;0));""). The rest is just a desperate try to get that cell empty — LeFun
Sorry for my terrible try at getting those codes to work haha — LeFun

BruceWayne BruceWayne · Accepted Answer · 2017-07-25T19:34:54

Without VBA, not sure how you could quickly do that. Perhaps a find/replace on your formula, but that would even find formulas that don't equate to "".

If you just want to clear such cells, this sub may work. Change the range to your required range:

Sub remove_Empty_Formula()
Dim rng As Range, cel As Range
Set rng = Range("B1:B10")

For Each cel In rng
    If WorksheetFunction.IsFormula(cel) And cel.Value = "" And cel.Offset(0, -1).Value <> "" Then
        cel.ClearContents
    End If
Next cel
End Sub

Excel: Center across selection with cells containing nothing (as in "") but formulas

1 Answers