visual basic excel 2000

[JoenMar]

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!

 

[Rick Rothstein]

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

 

[Chip Pearson]

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

 

[Tom Hutchins]

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

 

[Tom Hutchins]

(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

 

[Chip Pearson]

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

 

[Dave Peterson]

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.)