View Single Post
  #3   Report Post  
Dave O
 
Posts: n/a
Default

I posted a response earlier- or thought I did- but it has not appeared
yet. The code in that post is buggy, and should be replaced with the
following code.

Copy this code and paste it in as a macro. It stores the value of each
cell in a tab (except cells that contain formulas) to memory, erases
the contents of the cell, and replaces the contents with the scrubbed
value: no apostrophes, blanks, etc.

Sub TrueVal_Tab()
'Converts apparently non-blank cells, such as those containing
'spaces or an apostrophe, to blank cells in the current tab of
'a workbook. Useful for data imported from a database.

'Use and distribute freely. If you find this useful, and particularly
'if you are in a corporate setting and this bails you out of a serious
'jam, ask your company to make a donation to the American Diabetes
'Association, or the national Diabetes Foundation in your country.

'Set calc to manual
With Application
..Calculation = xlManual
..MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False

Dim TrueVal
Dim CurrentFormat As String
Dim Rows As Long, Columns As Long
Dim R As Long, C As Long

'determine lower right corner of this tab
ActiveCell.SpecialCells(xlLastCell).Select
Rows = Selection.Row
Columns = Selection.Column
'return to a1
Range("a1").Select

'Begin FOR loop to cycle through columns and rows
For C = 0 To Columns - 1
For R = 0 To Rows - 1

'if the cell contains a formula, skip
If Mid(ActiveCell.Offset(R, C).Formula, 1, 1) < "=" Then
TrueVal = Trim(ActiveCell.Offset(R, C).Value)
ActiveCell.Offset(R, C).Value = ""
ActiveCell.Offset(R, C).Value = TrueVal
End If

Next R
Next C

'set calc to auto
With Application
..Calculation = xlAutomatic
..MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False

Calculate
End Sub