"Blank" cells that are not really blank

[default_name]

Hello,

I have a large batch of data that, once generated has some blank cells in it.
I am attempting to count a column of the data, using the COUNTA function, to count the number of cells that have content in them.

Upon doing this, however, I realized that the COUNTA function was counting every cell in the range, regardless of whether or not it appeared to be blank.
I tested out a few of the "supposedly blank cells" with an ISBLANK function and discovered that although they appear blank, they are not being recognized as such by other formulas (they appear as FALSE with the ISBLANK function). It's as if they are ghosts.

I noticed that if I click in the "blank" cell, and hit enter (or F2+Enter) that the cell finally appears as a blank (as TRUE using the ISBLANK function).
I am currently using the following VBA code to try and run through this column and correct these ghost cells:

Sub mod1()
Range("K43:K20000").Select
    Dim r1 As Range, r2 As Range
    Set r2 = Selection
    For Each r1 In r2
        r1.Select
        Application.SendKeys "{f2}{enter}"
        DoEvents
    Next
End Sub

Not only does this code not work, but it takes forever to execute.
Is there a more simple way to solve this issue with this cell range?

Thanks in advance

 

[MARK858]

If all the cells in the range are constants not formulas then try....

Sub mod1b()
With Range("K43:K20000")
    .Value = .Value
End With
End Sub

 

[JoeMo]

Here's some code that may work. Select the range you want to cleanup first, then run the code.

Sub TrimALLMcRitchie()
   'David McRitchie 2000-07-03 mod 2002-08-16 2005-09-29 join.htm
   '-- http://www.mvps.org/dmcritchie/excel/join.htm#trimall
   ' - Optionally reenable improperly terminated Change Event macros
      Application.DisplayAlerts = True
      Application.EnableEvents = True   'should be part of Change Event macro
   If Application.Calculation = xlCalculationManual Then
      MsgBox "Calculation was OFF will be turned ON upon completion"
   End If
   
   Application.ScreenUpdating = False
   Application.Calculation = xlCalculationManual
   Dim cell As Range
   'Also Treat CHR 0160, as a space (CHR 032)
   Selection.Replace What:=Chr(160), replacement:=Chr(32), _
     lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
   Selection.Replace What:=Chr(13) & Chr(10), replacement:=Chr(32), _
        lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
   Selection.Replace What:=Chr(13), replacement:=Chr(32), _
        lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
   Selection.Replace What:=Chr(21), replacement:=Chr(32), _
        lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
   '---------------------------
   Selection.Replace What:=Chr(8), replacement:=Chr(32), _
      lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
   Selection.Replace What:=Chr(9), replacement:=Chr(32), _
      lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
   'Trim in Excel removes extra internal spaces, VBA does not
   On Error Resume Next
   For Each cell In Intersect(Selection, _
      Selection.SpecialCells(xlConstants, xlTextValues))
     cell.Value = Application.Trim(cell.Value)
   Next cell
   On Error GoTo 0
   Application.Calculation = xlCalculationAutomatic
   Application.ScreenUpdating = True
End Sub

 

[AmplifyMike]

This may be an overly simple approach but give it a try. Select the whole column, then do Text to Column. Choose fixed width and remove any of the lines that show up. This will not move any data to another column, but could remove your blank cell problem. This works for dates that stubbornly remain as text, even when formatted as data, but a click in the cell (or F2+Enter) finally converts it to a date.

 

[default_name]

This may be an overly simple approach but give it a try. Select the whole column, then do Text to Column. Choose fixed width and remove any of the lines that show up. This will not move any data to another column, but could remove your blank cell problem. This works for dates that stubbornly remain as text, even when formatted as data, but a click in the cell (or F2+Enter) finally converts it to a date.

Thanks guys!
Your solution, AmplifyMike, worked perfectly!

I tried your solutions, MARK858 and JoeMo, but executing them was taking a toll on Excel.

Thanks for all your help guys!

 

[MARK858]

Just to point out that you can use the delimited option in text to columns so you don't need to remove any lines as below...

Select Column K, click "Data" tab - click "Text To Columns" - select "Delimited" and click "Next", make sure "Other" is not checked ,click "Next" and click "Finish".

 

[default_name]

Thanks for your help!

Using your input I was able to compile the following VBA:

Range("K43:K20000").Select
    Selection.TextToColumns Destination:=Range("K43"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(0, 1), TrailingMinusNumbers:=True

 

[MARK858]

If you do away with the Select/Selection it will run smoother/quicker

Range("K43:K20000").TextToColumns Destination:=Range("K43"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(0, 1), TrailingMinusNumbers:=True

 

[default_name]

If you do away with the Select/Selection it will run smoother/quicker

Range("K43:K20000").TextToColumns Destination:=Range("K43"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(0, 1), TrailingMinusNumbers:=True

You are awesome!! Thank you so much!