Microsoft Office Forums - View Single Post - [Solved] Can a cell be both blank and not blank ?

MartinGM · 03-30-2025, 01:45 AM

Quote:

Originally Posted by Pecoflyer

A small Power Query solution will clean that up.
Text is highlighted in yellow. After transformation, the null string has disappeared

Code:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Replaced Value" = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,Table.ColumnNames(Source))
in
    #"Replaced Value"

Thanks again

I couldn't work out how to detect the errant cell in "pure" VBA but a simple use of the Worksheet functions did the trick - here's the critical line

Code:

If Application.CountA(Cell) = 1 And Application.CountBlank(Cell) = 1 Then

Now to find out how this happens

Martin