Replace all characters within a cell apart from the 1st with *

[hopeless88]

I relatively new to VBA so not exactly sure of the best way to achive this:

I am trying to anonymise a column of data. I need to replace all letters in the cell with an * apart from the first letter.

I have tried this:

Sub AnonymiseCAR()
Dim CounterCAR
Dim ClientName As String

'Select CAR Worksheet
Worksheets("RAW DATA - CAR").Activate
CounterCAR = 2
Do
ClientName = "E" & CounterCAR
If ClientName = "" Then
Exit Do
Else
Replace(ClientName,*,"*",2)

CounterCAR = CounterCAR + 1
Loop

However I get a Compile error for the Replace line because of a Syntax error.

Any suggestions on how to do this would be great!

Thanks

 

[mikerickson]

Something like

Dim oneCell as Range

With ThisWorkbook.Sheets("Raw data CAR").Range("E:E")
    For each oneCell in Range(.Cells(2, 1), .Cells(.Rows.Count, 1).End(xlup))
        oneCell.Value = Left(Left(oneCell.Value, 1) & String(Len(oneCell.Value), "*"), Len(oneCell.Value))
    Next oneCell
End With

 

[Rick Rothstein]

Here is another way for your to consider...

Dim Addr As String

Addr = "'Raw data CAR'!E2:E" & Cells(Rows.Count, "E").End(xlUp).Row
Range(Addr) = Evaluate(Replace("IF(LEN(@),LEFT(@)&REPT(""*"",LEN(@)-1),"""")", "@", Addr))

While it should be obvious, I think it important to point out that running either mikerickson's or my code will destroy (physically change) the data in Column E and you will not be able to Undo the changes made by the code.