Hi
Here is the macro which removes non-breaking spaces in all the cells of
the worksheet.
Public Sub RemoveNonBreakingSpaces()
' Remove all non-breaking spaces from constants in the active worksheet.
Dim Cell As Range
Dim Value As String
Dim Length As Long
If ActiveSheet.Type <> xlWorksheet Then Exit Sub
For Each Cell In ActiveSheet.UsedRange
If Len(Cell) > 0 And Not Cell.HasFormula Then
Value = Cell
Length = Len(Value)
Value = Replace(Value, Chr(160), vbNullString)
If IsNumeric(Value) Then Value = Trim(Value)
If Length <> Len(Value) Then Cell = Value
End If
Next Cell
End Sub
Best Regards
Praveen Khunte
Dave Bonallack wrote:
> Hi,
> If you don't need any merged cells in your sheet, try putting these
> lines at the beginning of your present code:
>
>
> Regards - Dave.
>
>
> ------------------------------------------------------------------------
> Date: Thu, 21 May 2009 23:11:30 +0530
> From: praveen.khu...@gmail.com
> To: excel-macros@googlegroups.com
> Subject: $$Excel-Macros$$ Need a macro to automatically merge cells in
> the worksheet/workbook
>
> Hi all
> How's everyone doing?
> I am novice to macro, I need a help from you guys. I hope it should be
> simple.*
> Present Scenario:* I have a macro which remove non-breaking space from
> all the cells at once in the worksheet. (If anyone need, I can share
> the code).
> *Usefulness of the Macro*: When you copy data from the internet and
> paste in an excel worksheet, it contains lot of spaces before and
> after, and when you want to perform some calculation it doesn't give
> desired result. This macro removes spaces before and after so that
> your calculation (life) becomes easy.*
> Problem:* This macro stops working beyond the point where the it finds
> a merged cell. To overcome this, I need to select all the cells of the
> worksheet and then Go to Format > Cells and click uncheck merge cells
> (as shown in image). This is tedious repetitive job since I have about
> 30 worksheets.*
> Requirement: *A macro which automatically merges all the cells of the
> worksheet (if possible-entire workbook).
>
>
>
> Warm Regards
> Praveen Khunte
>
>
> ------------------------------------------------------------------------
>
>
> ------------------------------------------------------------------------
> Let ninemsn property help Looking to move somewhere new this winter?
> <http://a.ninemsn.com.au/b.aspx?URL=http%3A%2F%2Fninemsn%2Edomain%2Ecom%2Eau%2F%3Fs%5Fcid%3DFDMedia%3ANineMSN%5FHotmail%5FTagline&_t=774152450&_r=Domain_tagline&_m=EXT>
> >
--~--~---------~--~----~------------~-------~--~----~
-------------------------------------------------------------------------------------
Some important links for excel users:
1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at
http://www.excelitems.com
2. Excel tutorials at http://www.excel-macros.blogspot.com
3. Learn VBA Macros at http://www.vbamacros.blogspot.com
4. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
If you find any spam message in the group, please send an email to:
Ayush Jain @ jainayus...@gmail.com or
Ashish Jain @ 26may.1...@gmail.com
-------------------------------------------------------------------------------------
-~----------~----~----~----~------~----~------~--~---
