help with char(160)+password vba

[avishain]

Hello all,

i need help with the following issue:

im using excel 2007,however,the spesific file is XLS (excel 2003).

got a table from A:K.

in columns A and J ,i got numbers which are formated as "General" and i want to keep it that way.
in some cases,the data has char(160) at the begining and i want to fix it.

now,i can use a function like TRIM(A1) or SUBSTITUTE($A1,CHAR(160),"")
but i want to use VBA .

another thing,

this sheet contains a pasword ("1234") ,so i want to assian a button and when i click it ,it will :

1.unlock the sheet with the password (1234)
2.remove from column A and J the char(160) or any space at the begining.
3.put a password again (1234).

thank u for helping.

 

[VoG]

Perhaps you can adapt the TrimAll macro http://dmcritchie.mvps.org/excel/join.htm

 

[AlphaFrog]

Sub AJ_Trimmer()
    
    ActiveSheet.Unprotect Password:="1234"
                    
    Range("A:A, J:J").Replace Chr(160), "", xlPart
    Range("A:A, J:J").Replace " ", "", xlPart
    
    ActiveSheet.Protect Password:="1234"
    
End Sub

 

[avishain]

Sub AJ_Trimmer()
 
    ActiveSheet.Unprotect Password:="1234"
 
    Range("A:A, J:J").Replace Chr(160), "", xlPart
    Range("A:A, J:J").Replace " ", "", xlPart
 
    ActiveSheet.Protect Password:="1234"
 
End Sub

it works partialy...

in some cases there is a space.... i tried the trim function and it helped.

can u give me a code to use the trim function on the same range? (A and J) and in addition, to paste it as values...

?

thanks