Clear "Blank" cells

[Steven B]

Hello all,

I have found, in a number of my workbooks, ranges that contain cells
that appear to be blank, but excel treats them as though they have
data. When I sort a column ascending, these cells end up at the top of
my list. Selecting and clearing them removes whatever value is hidden
there and they no longer sort to the top of my lists.

I'd like to be able to find them and clear all of them in my workbook
but I can't figure out how to "find" them.

Thoughts?



Steven

 

[Gary''s Student]

Try this:

Sub cleanup()
For Each r In ActiveSheet.UsedRange
If Len(r.Value) = 0 Then
r.Clear
End If
Next
End Sub

 

[Steven B]

Thank you Gary, running it now. Will report back on it's
effectiveness, but it looks like just the thing.

Would it execute faster to try something like this:

If Len(r.Value) = 0 Then
Rng = Union(IIf(Rng Is Nothing, R, Rng), R)
End if
If Not Rng Is Nothing Then Rng.Clear: Set OLMissing =
Nothing

I have disable screen updating, calculations and the status bar to
help, but it is slogging along.

 

[Steven B]

Thank you Gary, running it now. Will report back on it's
effectiveness, but it looks like just the thing.

Would it execute faster to try something like this:

If Len(r.Value) = 0 Then
 Rng = Union(IIf(Rng Is Nothing, R, Rng), R)
End if
            If Not Rng Is Nothing Then Rng.Clear: Set OLMissing =
Nothing

I have disable screen updating, calculations and the status bar to
help, but it is slogging along.

Correction - ignore the Set OLMissing = Nothing bit, copy/paste from
another script I use and didn't edit it properly.


S

 

[Steven B]

Hi Gary et al,

Here is my final code, this worked well and reasonably quickly

Sub cleanup() 'Clears "empty cells" in worksheet
Dim R As Range
Dim Rng As Range
Dim i As Long 'integer
Dim lrow As Long 'Last Row of data
Dim lcol As Long 'last column of data


With Application
.DisplayAlerts = False
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
lcol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
lrow = ActiveSheet.Cells(Rows.Count, "H").End(xlUp).row

For i = 1 To lcol
Range(Cells(1, i).Address & ":" & Cells(lrow, i).Address).Select
For Each R In Selection
If Len(R.Value) = 0 Then
Set Rng = Union(IIf(Rng Is Nothing, R, Rng), R)
End If
Next R
If Not Rng Is Nothing Then Rng.Clear: Set Rng = Nothing
Next i
With Application
.DisplayAlerts = True
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
.StatusBar = False
End With
End Sub

 

[Dave Peterson]

Saved from a previous post:

If you want to see what's left in that cell after you convert ="" to values,
try:
Tools|Options|Transition Tab|Toggle Transition Navigation keys on.

Then select one of those cells and look at the formula bar. You'll see an
apostrophe. (Don't forget to toggle the setting to off.)

When I want to clean up this detritus, I do this:

Select the range (ctrl-a a few times to select all the cells)
Edit|Replace
what: (leave blank)
with: $$$$$
replace all

Immediately followed by:
Edit|Replace
what: $$$$$
with: (leave blank)
replace all

In code you could do something like:

Option Explicit
Sub testme()
With ActiveSheet
With .cells 'or a specific range: With .Range("D")
.Replace what:="", replacement:="$$$$$", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False
.Replace what:="$$$$$", replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False
End With
End With
End Sub

 

[ryguy7272]

Sub Remove_CR_LF()
With Selection
..Replace What:=Chr(39), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
..Replace What:=Chr(146) & Chr(10), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
..Replace What:=Chr(180), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
End With
End Sub

Sub Remove_CR_LF()
With Selection
..Replace What:=Chr(160), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
..Replace What:=Chr(13) & Chr(10), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
..Replace What:=Chr(10), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
End With
End Sub

You have lots of response now. One of these must work...

HTH,
Ryan---

 

[Steven B]

Hi Dave,

Very cool, thank you. Worked like a charm!


Steven

 

[Steven B]

Hi Ryan,

That one didn't seem to do it. It doesn't see the character in the
cell, whether Transition Navigation keys is on or off. I've tried to
return the Character number of the ' in the cell, but I can't get
Excel to see it. Dave's solution, and my own work though Dave's is
much quicker.

Thank you for your help,


Steven