Prev: Application.VLookup and External files
Next: Disabling Built-in items on a menu
From: JoenMar on 20 May 2010 17:03
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!


From: Rick Rothstein on 20 May 2010 17:12
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" <JoenMar(a)discussions.microsoft.com> wrote in message
news:D0228C35-0D76-4655-863D-03F9BE3A46B0(a)microsoft.com...
> 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!
>
>
From: Chip Pearson on 20 May 2010 17:43
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
<JoenMar(a)discussions.microsoft.com> 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!
>
From: Tom Hutchins on 20 May 2010 17:59
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!
>
>
From: Tom Hutchins on 20 May 2010 18:00
(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!
>
>
 |  Next  |  Last
Pages: 1 2
Prev: Application.VLookup and External files
Next: Disabling Built-in items on a menu