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

bapcki1

New Member
Joined
Nov 12, 2011
Messages
39
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).

Code:
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
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
This is just a guess; Start at 2 rather than 1 (character 1 is the null character?)
 
Upvote 0
Would it be easier to just substring and then add the spaces onto the front?

Code:
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,226,730
Messages
6,192,711
Members
453,748
Latest member
akhtarf3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top