Jump to content
News Ticker
  • Welcome to the Community
  • Use the forum to troubleshoot your Excel & VBA Problems
  • We will be launching a new website soon! www.ExcelWTF.com/Learn ! Join us to learn VBA from the ground up!
  • 0
OldFella

Getting Rid of the Hidden Content of Blank Cells

Question

Hi Caleeco

Here's another simple little exercise. By that I'm assuming it will be simple for you, whereas it's impossible for me. It's all relative.

I posted a problem on the MSDN forum while you were sleeping during the Christmas / New Year break. I'm having massive grief with cells on my worksheets that appear blank, but lurking under the surface is "something" that makes a formula think that the cell is not blank. As a result the formula returns a wrong result, with horrible consequences.

 

One answer that was provided is:
"I think I found an easier way to clean up such problems. But off-hand, all I can think of is the following VBA procedure (select the Excel range to be cleaned first):

Sub doit()
For Each c In Selection
If Not IsEmpty(c) And Not c.HasFormula And Len(c.Value2) = 0 Then
     c.ClearContents
End If
Next
End Sub"

 

Since posting to MSDN, I've found that the problem is far more widespread that I first realized. It seems that many, many workbooks are suffering from this problem. That being the case I now intend to go back through every workbook and clear every blank cell with that coding - hundred of worksheets! I'm thinking to put the coding in Personal.xlsb so I can use it everywhere. To speed it up slightly I want to avoid having to "(select the Excel range to be cleaned first)". Can you please modify the coding so that instead of me having to manually select the range to be cleaned when I run the macro, the coding automatically selects the entire worksheet as the range defined by Selection.

 

Or - perhaps even better - can it be written so that I can apply "doit" to an entire workbook? That would really speed it up!

 

As previous - give your new website priority and resolve this for me when you have an idle moment.

 

Many thanks
David

 

PS - If you want a bit more background, the MSDN URL is https://social.msdn.microsoft.com/Forums/en-US/35ea60db-0c10-4178-a86c-08b83ad58a17/how-to-determine-hidden-cell-contents?forum=excel

Share this post


Link to post
Share on other sites

12 answers to this question

Recommended Posts

  • 0

Hi OldFella,

 

Thanks for the link & the values of 'c', it does shed some light on it. It appears as if one of your formulas is returning an error like a #N/A or #DIV0 so the VBA code has trouble evaluating it for length. I was struggling to recreate it on my spreadsheet, but from some reading online, this may help:

 

I have added an IFERROR statement, to bypass trying to evaluate its length with the LEN() function. 

 

Sub doit_for_AllWorksheets()
'// www.ExcelWTF.com
'// Purpose: Clear contents of problem cells
Dim ws As Worksheet
Dim c As Range
    
    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With
    
    For Each ws In Worksheets
        For Each c In ws.UsedRange
            If IsError(c.Value2) Then
                'Move on
            Else
                If Not IsEmpty(c) And Not c.HasFormula And Len(c.Value2) = 0 Then
                    c.ClearContents
                End If
            End If
        Next c
    Next ws
    
    With Application
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
    End With
End Sub

Let me know if it works, 

Many Thanks

 

Caleeco

Share this post


Link to post
Share on other sites
  • 0

Hi David, 

 

Thanks for the question! Hmm that's an interesting problem, not come across it myself.

 

Don't worry, answering question here will always take preference! The new website it slow progress anyway :P I had a crack at doing what you suggested. 

 

Sub doit_for_AllWorksheets()
'// www.ExcelWTF.com
'// Purpose: Clear contents of problem cells
Dim ws As Worksheet
Dim c As Range
    
    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With
    
    For Each ws In Worksheets
        For Each c In ws.UsedRange
            If Not IsEmpty(c) And Not c.HasFormula And Len(c.Value2) = 0 Then
                c.ClearContents
            End If
        Next c
    Next ws
    
    With Application
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
    End With
End Sub

I've used the USEDRANGE property (so you don't have to select the cells), however i'm not sure if it will pick up your problem cells. I can modify the code to just check ALL cells on every sheet, but that might take a while. So worth testing to see if this shortcut works first. 

 

Let me know how you get on

Thanks

Caleeco

Share this post


Link to post
Share on other sites
  • 0

Hi Caleeco

 

No joy with the first run. I found a problem cell quite readily, on the sheet that I'm in the midst of upgrading (see attached). The selected cell is the one with the gremlin hiding in the depths.

 

Running the code gave the result as also attached.

2cgfud3.png

Thanks

David

(Sorry - couldn't figure out how to upload the images so that I could then insert them, so I've emailed them)

Share this post


Link to post
Share on other sites
  • 0
8 minutes ago, OldFella said:

Hi Caleeco

 

No joy with the first run. I found a problem cell quite readily, on the sheet that I'm in the midst of upgrading (see attached). The selected cell is the one with the gremlin hiding in the depths.

 

Running the code gave the result as also attached.

 

Thanks

David

(Sorry - couldn't figure out how to upload the images so that I could then insert them, so I've emailed them)

 

If you upload using tinypic.com or something similar you can paste images with IMG tags :)

 

I haven't received any emails as of yet, will check again soon.

 

Caleeco

Share this post


Link to post
Share on other sites
  • 0
On 1/28/2018 at 12:25 AM, OldFella said:

Hi Caleeco

 

No joy with the first run. I found a problem cell quite readily, on the sheet that I'm in the midst of upgrading (see attached). The selected cell is the one with the gremlin hiding in the depths.

 

Running the code gave the result as also attached.

2cgfud3.png

Thanks

David

(Sorry - couldn't figure out how to upload the images so that I could then insert them, so I've emailed them)

 

Hi David, 

 

I added the image you emailed across for reference. That error is occurring because the = 0 Then text should be at the end of the line highlighted in blue. So that line should read:

If Not IsEmpty(c) And Not c.HasFormula And Len(c.Value2) = 0 Then

 

Can you try making this change and re-running the code?

 

Alternatively, you could add the IFERROR statement to your formula

=IFERROR(A1/B1,"")

 

Let me know how you get on :)

Thanks

Caleeco

Share this post


Link to post
Share on other sites
  • 0

Sorry for the delay - I finally got around to amending the code and running it. I'm delighted to say that the outcome is stunning. It purged a 22-sheet workbook in about 15 seconds. I then ran Ctrl-G / Blanks and was horrified with what I saw. I had regularly been finding cells on the various worksheets that weren't responding as they should, or that formulae were missing from odd cells. Using your code, followed by Ctrl-G, I can now see exactly where the problems are and can resolve them. Brilliant!! That's one beer I owe you!

 

I'm now going to sit here and draft up your next beer-earning exercise. It's convoluted, as you'll eventually see, so it's going to take me a while to very carefully outline what I'm trying to achieve. Probably won't complete it tonight so don't hold your breath.

 

Thanks again for sorting this current issue.

  • Like 1

Share this post


Link to post
Share on other sites
  • 0
1 hour ago, OldFella said:

Sorry for the delay - I finally got around to amending the code and running it. I'm delighted to say that the outcome is stunning. It purged a 22-sheet workbook in about 15 seconds. I then ran Ctrl-G / Blanks and was horrified with what I saw. I had regularly been finding cells on the various worksheets that weren't responding as they should, or that formulae were missing from odd cells. Using your code, followed by Ctrl-G, I can now see exactly where the problems are and can resolve them. Brilliant!! That's one beer I owe you!

 

I'm now going to sit here and draft up your next beer-earning exercise. It's convoluted, as you'll eventually see, so it's going to take me a while to very carefully outline what I'm trying to achieve. Probably won't complete it tonight so don't hold your breath.

 

Thanks again for sorting this current issue.

 

No worries mate, glad I could help! Thanks for marking the thread as solved :D 

 

Haha, I like beer... so I look forward to the next opportunity to earn some! Ok cool, just post up when you've devised the problem statement and I'll take a look.

 

Thanks

Caleeco

Share this post


Link to post
Share on other sites
  • 0

Hi Caleeco

Sorry to have to re-open this one but I have a new issue. As the problems are close to being resolved in the Selectively Writing Horizontally Occurring Values to a Column thread, I'm now preparing the main worksheet to receive data from the FO_RV worksheet. The first issue I've discovered is text being displayed when it shouldn't, as a result once again of an invisible, underlying object. The date and time shown here should only be displayed if there's a value in any of the four preceding 'blank' cells.

 

fu21hsmi61koe1nc.png

 

"No worries", thought I. "I'm now armed with 'DoIt'!" So I ran 'DoIt' and was greeted with a Runtime error popup:

 

tkfqm1rfuz6ak1on.png

 

I then ran Debug and was presented with the following:

k4dddqex8r5gxl48.png

 

What have I screwed up this time, and how do I get 'DoIt' to run properly?

Share this post


Link to post
Share on other sites
  • 0

Marked as unsolved! 

 

Hmm not sure what's up there. Can you please do the following:

  1. Run the code again
  2. Run the Debug when you get the error
  3. The hover your mouse over the letter 'c' and tell me what pops up in the textbox

It will give me a clue as to what the problem is!

Many Thanks

 

Caleeco

Share this post


Link to post
Share on other sites
  • 0

Hi Caleeco

Not sure which 'c' you meant so I checked each of them:

IsEmpty(c)   -   c = Error 2042

c.HasFormula   -   c.HasFormula = True

(c.Value2)   -   c.Value2 = Error 2042

 

I'm sure you'll have a solution to this but since I'm always up for solving my own problems wherever possible I did a bit of a scroll round the 'net. I won't mess with your coding, but http://www.vbaexpress.com/forum/showthread.php?25189-Solved-vlookup-error-2042 

looked like it could be helpful.

 

Thanks

David

 

 

Share this post


Link to post
Share on other sites
  • 0

And another one bites the dust!!

 

Well done, that solved it. The errant date + time has now disappeared off the worksheet, and presumably from every other location in the workbook that these annoying, invisible gremlins were lurking.

 

I suspect that one of the comments in the MSDN thread had it nailed. My routine practice, given the complexity of these worksheets, is to spend time developing code in a small section of the sheet. Once I get it fully functional then I copy that block down, or across, the sheet. Once I've proven that sheet, then I clone it and make new, fully operational sheets. The suggestion from MSDN was that the problem you've just resolved is not an issue with my existing formulae, but with the output of a previous formula from a previous iteration of the sheet that resulted in a null, or #N/A, or #DIV0, or whatever that lurks unseen beneath the apparently blank cell, waiting to cause havoc.

 

The new coding should fully take care of this though and I'm making up a schedule/check list that will ensure that each time I open up an existing workbook I run DoIt to purge the book of these vermin!

 

Thanks again. I'll now re-mark it as Solved

  • Thanks 1

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×