blank cell but isblank returns false

I have a workbook with many blank  non-adjacent cells but isblank returns false and =code(a1) returns #VALUE

what is inside these cells and why code shows error and isblank shows false and how to clean them up without using VBA

i tried with trim and clean functions but both of them failed.

any help is appreciated.
LVL 3
excelismagicAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
please attach an example file so we can see...
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
note that there are some "known" scenarios where the cell "looks" emtpy, but is not:
https://support.microsoft.com/en-us/kb/823838
This behavior may occur when the cell contains a zero-length string. A zero length string may be a result of the following conditions:
    A formula.
    A copy and paste operation.
    A cell that contains a zero-length string is imported from a database that supports zero-length strings and that contains zero-length strings.

Open in new window


you exclude the formula, and I presume the cell does not contain a string with only blanks
leaves you with the scenarios above
0
excelismagicAuthor Commented:
thanks Guy Hengel [angelIII / a3]

i have attached the workbook.  please see the A1 cell.
Book1.xlsx
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
I confirm it "looks" like empty, but is not (explained by the above link)
the other function you may use to "check" is len(A1), it shall return 0 if the value is "empty"
0
excelismagicAuthor Commented:
thanks Guy Hengel [angelIII / a3]   so what could be the solution for this.

i checked the LEN is zero.  but problem is that if i use special select BLANK also it cannot work.

i want to select all cells in my worksheet that has this problem and basically replace them or delete them.

is there any non-VBA solution for this?
0
David SankovskySenior SysAdminCommented:
You can simply use this:
=IF(AND(ISBLANK(A1),LEN(A1)=0),"Relly Empty","Problematic cell")
If the cell is actually blank, it'll tell you so, other wise, it will record the cell as problematic.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You may try something like this to see if this works for you....
Sub DeleteBlankCells()
Dim cell As Range
With Application
    .Calculation = xlCalculationManual
    .EnableEvents = False
    .ScreenUpdating = False
End With
For Each cell In ActiveSheet.UsedRange    
    If cell.HasFormula = False And WorksheetFunction.CountA(cell) = 1 And WorksheetFunction.Count(cell) = 0 And WorksheetFunction.CountIf(cell, "?*") = 0 Then
        cell.ClearContents
    End If
Next cell
With Application
    .Calculation = xlCalculationAutomatic
    .EnableEvents = True
    .ScreenUpdating = True
End With
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>is there any non-VBA solution for this?
I would say: no to the non-vba solution
0
excelismagicAuthor Commented:
Thank you guys
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome. Happy to help. :)

I think the another workaround this is, create a helper column with the following formula and then apply filter to show the True Values and then select the visible cells in the desired column and delete them.

The following formula would return either True or False.

=AND(COUNTA(A1),COUNT(A1)=0,COUNTIF(A1,"?*")=0)

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.