Prev: Error in formula after auto-filtered
Next: Changing position of shapes in chart
From: Pnoahjones on 25 Mar 2010 00:13
when you have a series of data and you have a space or error in your numbers
when you are using functions and formulas. how do you find where there error
is in the data if you cant see the space or cant see where the error might be
that is causing the formula answer to be wrong.
From: macropod on 25 Mar 2010 00:29
Hi Pnoahjones,

Numbers with spaces are treated by Excel as text. Provided you're using the default cell format, rather than left-aligned, the text
strings will be right-aligned.

You can clean up a selected range, or a whole worksheet using the macro below:
Sub TrimRange()
Dim SBar As Boolean, Cell As Range, CellCount As Long, I As Long
SBar = Application.DisplayStatusBar
Application.DisplayStatusBar = True
Application.ScreenUpdating = False
Application.Calculation = xlManual
On Error Resume Next
I = 0
If Selection.Rows.Count * Selection.Columns.Count > 1 Then
CellCount = Selection.Rows.Count * Selection.Columns.Count
Else
CellCount = ActiveSheet.UsedRange.Rows.Count * ActiveSheet.UsedRange.Columns.Count
End If
For Each Cell In Selection.SpecialCells(xlConstants)
Cell.Value = Application.Trim(Replace(Cell.Value, Chr(160), " "))
I = I + 1
Application.StatusBar = Int(I / CellCount * 100 + 0.5) & "% Trimmed"
Next Cell
Application.Calculation = xlAutomatic
Application.StatusBar = False
Application.DisplayStatusBar = SBar
Application.ScreenUpdating = True
MsgBox "Finished trimming " & CellCount & " cells.", 64
End Sub

The macro gives a progress report on the Excel status bar and a message box when it's finished.

--
Cheers
macropod
[Microsoft MVP - Word]


"Pnoahjones" <Pnoahjones(a)discussions.microsoft.com> wrote in message news:A2862477-CA2B-4BBE-A85B-6C967DE75D37(a)microsoft.com...
> when you have a series of data and you have a space or error in your numbers
> when you are using functions and formulas. how do you find where there error
> is in the data if you cant see the space or cant see where the error might be
> that is causing the formula answer to be wrong.

 | 
Pages: 1
Prev: Error in formula after auto-filtered
Next: Changing position of shapes in chart