vzachin
05-08-2008, 07:42 AM
hi
i'm having problem with deleting an asterik in a field:
cl.Replace what:="~*", Replacement:=""
for the most part it works however if i have a field with all zeros (0), it removes the asterik and retains one zero. i need to retain all the zeros.
the following code removes spaces & asteriks. how can i modify the coding to retain the zeros?
Sub RemoveSpaces()
Dim cl As Variant
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set rng = Range("A5:Z5").Resize(LastRow - 4).SpecialCells(xlCellTypeVisible)
If Not rng Is Nothing Then
For Each cl In rng
If Len(cl) > Len(WorksheetFunction.Trim(cl)) Then
cl.Replace what:="~*", Replacement:=""
cl.Value = WorksheetFunction.Trim(cl)
End If
Next cl
End If
End Sub
thanks
zach
i'm having problem with deleting an asterik in a field:
cl.Replace what:="~*", Replacement:=""
for the most part it works however if i have a field with all zeros (0), it removes the asterik and retains one zero. i need to retain all the zeros.
the following code removes spaces & asteriks. how can i modify the coding to retain the zeros?
Sub RemoveSpaces()
Dim cl As Variant
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set rng = Range("A5:Z5").Resize(LastRow - 4).SpecialCells(xlCellTypeVisible)
If Not rng Is Nothing Then
For Each cl In rng
If Len(cl) > Len(WorksheetFunction.Trim(cl)) Then
cl.Replace what:="~*", Replacement:=""
cl.Value = WorksheetFunction.Trim(cl)
End If
Next cl
End If
End Sub
thanks
zach