WHY WONT THIS WORK ?! - VBA to replace character numbers 1 - 31 ...

[bapcki1]

Hi Everyone!

I dont understand why this code doesn't work.

Can anybody help me please?

I want to replace character numbers 1 - 31 with a space.

The code below results in all my data being replaced by a space (so essentially it deletes my data).

Sub Replace_Characters_One_To_ThirtyOne()
Dim i As Integer

For i 1 To 31

ThisWorkbook.Sheets1.Select
ThisWorkbook.Sheets1.Range("A:A").Select
Selection.Replace What:=Chr(i), Replacement:="", LookAt:=xlPart, MatchCase:=False

Next i

End Sub

I anyone could help I would be most grateful ! :D

Many thanks,


Paddy

 

[p45cal]

This is just a guess; Start at 2 rather than 1 (character 1 is the null character?)

 

[becci.gott]

Would it be easier to just substring and then add the spaces onto the front?

Sub change_first31()

For Each cell In Range("A:A")
    If cell.Value <> "" Then
        cell.Value = "                               " & Mid(cell.Value, 32, Len(cell.Value))
    End If
Next cell

End Sub

 

[bapcki1]

AWESOME! - that's seems to have done the trick!

How do you show that a thread has been solved please?

Many thanks, p45cal !:D


Paddy

 

[bapcki1]

Thank you for your response becci.gott ! :D

I appreciate your time and effort. I must have not explained myself as articulately as possible in the original question.

I was looking to replace the special characters numbers 1 to 31 with spaces.

p45cal's response seems to have solved the problem.

Many thanks again!


Paddy