find & replace (hidden) '

[JulieD]

hi

how can i write code to search a worksheet & replace all hidden ' with
nothing when the cell is (otherwise) empty

Cheers
JulieD

 

[Jim Cone]

JulieD,

Using "Trim" on each cell in the range will remove '.
However, Trim wipes out formulas and balks at cell errors.
So set up a range using "Special Cells" to select constants and text values or
check each cell as you come to it...
'-----------------------------------
Sub GetThemCritters()
Dim objCell As Range
For Each objCell In ActiveSheet.UsedRange 'or Selection
If Not IsError(objCell) And Not objCell.HasFormula Then _
objCell.Value = Trim$(objCell.Value)
Next 'objCell
End Sub
'-------------------------------------

Regards,
Jim Cone
San Francisco, CA
'----------------------------------------

 

[JulieD]

Hi Jim

thanks so much ... this has saved me HOURS of work ...

is it possible to easily exclude a range - the used range of the workbook is
A1:J464 (maybe more / less rows in the future) and i need to it for
everything EXCEPT column B

cheers
JulieD

 

[Jim Cone]

Julie,

I assume you have leading or trailing spaces in Column B that
you want to retain so...
Select the range to trim or select multiple ranges and use this
revised code. It first goes to each area selected and
runs the Trim function and then moves to the next selected
area and does the same.

'------------------------
Sub GetThemCrittersII()
Dim objCell As Range
Dim objArea As Range

For Each objArea In Selection.Areas
For Each objCell In objArea
If Not IsError(objCell) And Not objCell.HasFormula Then _
objCell.Value = Trim$(objCell.Value)
Next 'objCell
Next 'objArea

Set objCell = Nothing
Set objArea = Nothing
End Sub
'-------------------------------
Regards,
Jim Cone
San Francisco, CA

Hi Jim
thanks so much ... this has saved me HOURS of work ...
is it possible to easily exclude a range - the used range of the workbook is
A1:J464 (maybe more / less rows in the future) and i need to it for
everything EXCEPT column B
cheers
JulieD

- snip-

 

[Jim Cone]

JulieD,

I just discovered that Ragdyer posted an answer to you in the Misc group
His answer is easier to implement.
Multiple posts can aggravate people...

Regards,
Jim Cone
San Francisco, CA

 

[JulieD]

Hi Jim

sorry it wasn't meant to be a multiple post - i posted it there and then
realised that i needed a code rather than a once off solution as this data
is going to be a regular import - so your solution is the one i need to
implement.

Regards
julieD