none
How to set a cell to "Blank" using an Excel function RRS feed

  • Question

  • Hi all,

    I would like to set a cell using a function to a status, so that IsEmpty(...) returns True. Any idea how to accomplish this? In VBA its easy with "Range("....").ClearContents".

    Many thanks in advance.


    Regards Uwe

    Tuesday, August 12, 2014 5:53 PM

Answers

  • The question is crystal clear at least to me. You have formulas to generate values to be displayed in a chart. If a cell is blank, the chart displays literally nothing (which is good). Otherwise (e.g. the Excel formula returns the empty string) the cell is processed as being valued 0 and displayed on the chart accordingly.

    If one has a graph to display scoring where the denominator becomes 0 then the scoring is sort of infinite and the chart should display nothing at all. I will put a comment when copying the chart as an image in my powerpoint deck. Problem is the related cells are processed as being valued 0 and one gets an ugly chart.

    It could be easily fixed using VBA but the purpose here is to provide users with a macro-free template so they can edit the input, press F9 or save, and get all the updated reporting charts.

    I understand from the answers below it is just impossible in Excel.

    Thanks

    William

    • Marked as answer by Jörg Debus Saturday, April 4, 2020 2:25 PM
    • Unmarked as answer by Jörg Debus Saturday, April 4, 2020 2:29 PM
    • Marked as answer by Jörg Debus Saturday, April 4, 2020 2:30 PM
    Wednesday, August 28, 2019 2:39 AM

All replies

  • Use the following loops.

    The loop will return any cell that is blank.

    Sub Loop_Example()
        'http://www.rondebruin.nl/win/s4/win001.htm
        Dim Firstrow As Long
        Dim Lastrow As Long
        Dim Lrow As Long
        Dim CalcMode As Long
        Dim ViewMode As Long
    
        With Application
            CalcMode = .Calculation
            .Calculation = xlCalculationManual
            .ScreenUpdating = False
        End With
    
        'We use the ActiveSheet but you can replace this with
        'Sheets("MySheet")if you want
        With ActiveSheet
    
            'We select the sheet so we can change the window view
            .Select
    
            'If you are in Page Break Preview Or Page Layout view go
            'back to normal view, we do this for speed
            ViewMode = ActiveWindow.View
            ActiveWindow.View = xlNormalView
    
            'Turn off Page Breaks, we do this for speed
            .DisplayPageBreaks = False
    
            'Set the first and last row to loop through
            Firstrow = .UsedRange.cells(1).Row
            Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
    
            'We loop from Lastrow to Firstrow (bottom to top)
            For Lrow = Lastrow To Firstrow Step -1
    
                'We check the values in the A column in this example
                With .cells(Lrow, "A")
    
                    If IsEmpty(.Value) Then .Value = "True"
    
                        'If .Value = IsEmpty Then .Value = "True"
                        'This will delete each row with the Value "ron"
                        'in Column A, case sensitive.
    
                    'End If
    
                End With
    
            Next Lrow
    
        End With
    
        ActiveWindow.View = ViewMode
        With Application
            .ScreenUpdating = True
            .Calculation = CalcMode
        End With
    
    End Sub


    Please do not forget to click “Vote as Helpful” if the reply helps/directs you toward your solution and or "Mark as Answer" if it solves your question. This will help to contribute to the forum.

    Tuesday, August 12, 2014 6:35 PM
  • Re:  Using IsEmpty

    If you have an Excel function in a cell, the cell will never be "Empty" no matter what value you have the function return.
    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    Wednesday, August 13, 2014 4:12 AM
  • Re:  Using IsEmpty

    If you have an Excel function in a cell, the cell will never be "Empty" no matter what value you have the function return.
    '---
    Jim Cone

    Hello Jim,

    I'm not sure whether I have understood you correctly, but afaik Excel functions can target other cells with their results. So blanking a cell would easily work from cell A1 where the function is coded and return True/False as a result: =Blank(A2). Any idea whether there is something around like this?


    Regards Uwe

    Wednesday, August 13, 2014 3:27 PM
  • Re:  "I'm not sure whether I have understood you correctly"
    Maybe this...

      =ISBLANK(A2)
     -or-
      =LEN(A2)=0

    Note that a single apostrophe  " ' " in a cell is read differently by the above.
    '---
    Jim Cone
    Wednesday, August 13, 2014 4:03 PM
  • The question is crystal clear at least to me. You have formulas to generate values to be displayed in a chart. If a cell is blank, the chart displays literally nothing (which is good). Otherwise (e.g. the Excel formula returns the empty string) the cell is processed as being valued 0 and displayed on the chart accordingly.

    If one has a graph to display scoring where the denominator becomes 0 then the scoring is sort of infinite and the chart should display nothing at all. I will put a comment when copying the chart as an image in my powerpoint deck. Problem is the related cells are processed as being valued 0 and one gets an ugly chart.

    It could be easily fixed using VBA but the purpose here is to provide users with a macro-free template so they can edit the input, press F9 or save, and get all the updated reporting charts.

    I understand from the answers below it is just impossible in Excel.

    Thanks

    William

    • Marked as answer by Jörg Debus Saturday, April 4, 2020 2:25 PM
    • Unmarked as answer by Jörg Debus Saturday, April 4, 2020 2:29 PM
    • Marked as answer by Jörg Debus Saturday, April 4, 2020 2:30 PM
    Wednesday, August 28, 2019 2:39 AM