That is AWESOME Bernie!

JC, I am curious...did your data originally come from Outlook? I just
finished a project which required me to export thousands of contacts from
Outlook to Excel. All contacts came into Excel with €˜hard returns in the
address areas. I put a post on this DG a short time ago, and someone gave me
the macro below. The macro will remove all €˜hard returns from your SS.

Just before running this macro, select the small square between A and 1 in
the upper left hand corner of your SS.

Sub Remove_CR_LF()
With Selection
..Replace What:=Chr(160), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
..Replace What:=Chr(13) & Chr(10), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
..Replace What:=Chr(10), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
End With
End Sub


Regards,
Ryan---


--
RyGuy


"Bernie Deitrick" wrote:

JC,

39 is not the code for a tick - it's the code for a single quote, which won't show - so all you are
really doing is converting all the filled cells to values:

Sub TryNow()
Dim myA As String
Dim mySht As Worksheet

myA = "E4:E2300"

For Each mySht In Worksheets
mySht.Range(myA).Value = mySht.Range(myA).Value
Next mySht

End Sub

HTH,
Bernie
MS Excel MVP


"JC" wrote in message
...

VBA help
to add tickmark to range that has data only trying this code
'Format the column to Add Ticks
Dim X As Variant

If WorksheetFunction.CountA(Range("E4:E2300")) 0 Then
If Len(X) 0 Then 'Find cells with data
X.FormulaR1C1 = Chr(39) & X.Text '39 is code for tick
Else
X.FormulaR1C1 = "" 'If empty do not put tick
End If
End If

end sub

This works but it takes too long to go through the range and when I have
19 sheets to have 1hr just to format too long

' Format the column to Add Ticks
' Dim Z As Variant, X As Variant
' 'Range("E4:E" & Lastrow).Select

' Range("E4").Select
' Range(Selection, Selection.End(xlDown)).Select

' Z = Selection.Address 'get the address
' For Each X In ActiveSheet.Range(Z) 'Do while
' If Len(X) 0 Then 'Find cells with data
' X.FormulaR1C1 = Chr(39) & X.Text '39 is code for tick
' Else
' X.FormulaR1C1 = "" 'If empty do not put tick
'End If
'Next

End Sub