Prev: Application.VLookup and External files
Next: Disabling Built-in items on a menu
From: Chip Pearson on 20 May 2010 18:25

> 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 <chip(a)cpearson.com>
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
><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: Dave Peterson on 20 May 2010 18:44
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 here:
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
First  |  Prev  | 
Pages: 1 2
Prev: Application.VLookup and External files
Next: Disabling Built-in items on a menu