Someone in our office used the spacebar to make some cells in a database look
blank when they were not. I discovered this when trying to write a macro to
manipulate the database with code that used the end up/down, etc. command and
was being interrupted by visually blank spaces that were created by the
spacebar.

I am trying to write code to totally clear only the cells which appear to be
blank and not remove the cells with good data.

I tried to select the general area including the data and use an If then
statement to look at the cells in the selection and clear only the ones who's
value = 0. However I don't know the proper code to select the cells that
might equal zero in this Selection or Range. I'm getting error code 13
mismatches, etc.

Help!

I think this macro will do what you want....

Sub ClearCellsThatLookBlank()
Dim Cell As Range
For Each Cell In ActiveSheet.UsedRange.Cells
If Len(Cell.Value) 0 And Len(Trim(Cell.Value)) = 0 Then Cell.Clear
Next
End Sub

--
Rick (MVP - Excel)



"JoenMar" wrote in message
...
Someone in our office used the spacebar to make some cells in a database
look
blank when they were not. I discovered this when trying to write a macro
to
manipulate the database with code that used the end up/down, etc. command
and
was being interrupted by visually blank spaces that were created by the
spacebar.

I am trying to write code to totally clear only the cells which appear to
be
blank and not remove the cells with good data.

I tried to select the general area including the data and use an If then
statement to look at the cells in the selection and clear only the ones
who's
value = 0. However I don't know the proper code to select the cells that
might equal zero in this Selection or Range. I'm getting error code 13
mismatches, etc.

Help!

Select the cells whose values you want to test, and then run the
following code:

Sub AAA()
Dim R As Range
Dim RR As Range
Set RR = Selection.SpecialCells( _
xlCellTypeConstants, xlTextValues)
For Each RR In R
If Len(Trim(R.Text)) = 0 Then
R.Value = vbNullString
End If
Next RR
End Sub

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com





On Thu, 20 May 2010 14:03:01 -0700, JoenMar
wrote:

Someone in our office used the spacebar to make some cells in a database look
blank when they were not. I discovered this when trying to write a macro to
manipulate the database with code that used the end up/down, etc. command and
was being interrupted by visually blank spaces that were created by the
spacebar.

I am trying to write code to totally clear only the cells which appear to be
blank and not remove the cells with good data.

I tried to select the general area including the data and use an If then
statement to look at the cells in the selection and clear only the ones who's
value = 0. However I don't know the proper code to select the cells that
might equal zero in this Selection or Range. I'm getting error code 13
mismatches, etc.

Help!

For Each RR In R
should be
For Each R In RR

Sorry about that.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com


On Thu, 20 May 2010 16:43:59 -0500, Chip Pearson
wrote:

Select the cells whose values you want to test, and then run the
following code:

Sub AAA()
Dim R As Range
Dim RR As Range
Set RR = Selection.SpecialCells( _
xlCellTypeConstants, xlTextValues)
For Each RR In R
If Len(Trim(R.Text)) = 0 Then
R.Value = vbNullString
End If
Next RR
End Sub

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com





On Thu, 20 May 2010 14:03:01 -0700, JoenMar
wrote:

Someone in our office used the spacebar to make some cells in a database look
blank when they were not. I discovered this when trying to write a macro to
manipulate the database with code that used the end up/down, etc. command and
was being interrupted by visually blank spaces that were created by the
spacebar.

I am trying to write code to totally clear only the cells which appear to be
blank and not remove the cells with good data.

I tried to select the general area including the data and use an If then
statement to look at the cells in the selection and clear only the ones who's
value = 0. However I don't know the proper code to select the cells that
might equal zero in this Selection or Range. I'm getting error code 13
mismatches, etc.

Help!

Try this macro:

Sub RemoveBlanks()
Dim x As Range, Cntr As Long
On Error GoTo RBerr
Cntr = 0
Cells.Select
Selection.SpecialCells(xlCellTypeConstants, 23).Select
For Each x In Selection
If Len(Trim(x.Value)) = 0 Then
x.Value = vbNullString
Cntr = Cntr + 1
End If
Next x
MsgBox Cntr & " cells were updated"
Exit Sub
RBerr:
MsgBox Err.Description, , "RemoveBlanks"
End Sub

Hope this helps,

Hutch

"JoenMar" wrote:

Someone in our office used the spacebar to make some cells in a database look
blank when they were not. I discovered this when trying to write a macro to
manipulate the database with code that used the end up/down, etc. command and
was being interrupted by visually blank spaces that were created by the
spacebar.

I am trying to write code to totally clear only the cells which appear to be
blank and not remove the cells with good data.

I tried to select the general area including the data and use an If then
statement to look at the cells in the selection and clear only the ones who's
value = 0. However I don't know the proper code to select the cells that
might equal zero in this Selection or Range. I'm getting error code 13
mismatches, etc.

Help!

(2nd post - not sure it uploaded) Try this macro:

Sub RemoveBlanks()
Dim x As Range, Cntr As Long
On Error GoTo RBerr
Cntr = 0
Cells.Select
Selection.SpecialCells(xlCellTypeConstants, 23).Select
For Each x In Selection
If Len(Trim(x.Value)) = 0 Then
x.Value = vbNullString
Cntr = Cntr + 1
End If
Next x
MsgBox Cntr & " cells were updated"
Exit Sub
RBerr:
MsgBox Err.Description, , "RemoveBlanks"
End Sub

Hope this helps,

Hutch

"JoenMar" wrote:

Someone in our office used the spacebar to make some cells in a database look
blank when they were not. I discovered this when trying to write a macro to
manipulate the database with code that used the end up/down, etc. command and
was being interrupted by visually blank spaces that were created by the
spacebar.

I am trying to write code to totally clear only the cells which appear to be
blank and not remove the cells with good data.

I tried to select the general area including the data and use an If then
statement to look at the cells in the selection and clear only the ones who's
value = 0. However I don't know the proper code to select the cells that
might equal zero in this Selection or Range. I'm getting error code 13
mismatches, etc.

Help!

Instead of looping through all the cells in the worksheet, I think I'd just do a
few edit|Replaces.
But you'll have to decide if the users aren't really out to get you.

Option Explicit
Sub testme()
Dim myRng As Range
Dim iCtr As Long
Dim MaxSpacesToFix As Long

MaxSpacesToFix = 10

With Worksheets("Sheet1")
Set myRng = .Cells 'or some specific range like .Range("A1:X99")
End With

For iCtr = 1 To MaxSpacesToFix
myRng.Replace what:=Space(iCtr), _
replacement:="", _
lookat:=xlWhole, _
searchorder:=xlNext, _
MatchCase:=False
Next iCtr

End Sub

Adjust the maxspacestofix to what you know(?) it can't exceed.

If you're new to macros:

Debra Dalgleish has some notes how to implement macros he
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)



JoenMar wrote:

Someone in our office used the spacebar to make some cells in a database look
blank when they were not. I discovered this when trying to write a macro to
manipulate the database with code that used the end up/down, etc. command and
was being interrupted by visually blank spaces that were created by the
spacebar.

I am trying to write code to totally clear only the cells which appear to be
blank and not remove the cells with good data.

I tried to select the general area including the data and use an If then
statement to look at the cells in the selection and clear only the ones who's
value = 0. However I don't know the proper code to select the cells that
might equal zero in this Selection or Range. I'm getting error code 13
mismatches, etc.

Help!

--

Dave Peterson