简单excel宏工具对数据进行脱敏

简单excel宏工具对数据进行脱敏

宏代码

Sub aa()
    Dim col As String
    Dim maxrow
    Dim stratpoint&, changdu&
    Set Rg = Application.InputBox("请您选择需要脱敏的数据后一列！", Title:="提示", Type:=8)
    stratpoint = Val(Application.InputBox("请您输入脱敏的起始位置？"))
    If stratpoint = 0 Then MsgBox "您未输入脱敏的起始位置，程序退出！": Exit Sub
    changdu = Val(Application.InputBox("请您输入脱敏的数据长度？"))
    If changdu = 0 Then MsgBox "您未输入脱敏的数据长度，程序退出！": Exit Sub
    col = Mid(Rg.Address, 2, 1)
    maxrow = ActiveSheet.Cells(1048576, 1).End(xlUp).row
    Columns(col & ":" & col).Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range(col & "1").Select
    ActiveCell.FormulaR1C1 = "=REPLACE(RC[-1]," & stratpoint & "," & changdu & ",REPT(""*""," & changdu & "))"
    Range(col & "1").Select
    Selection.AutoFill Destination:=Range(col & "1:" & col & maxrow), Type:=xlFillDefault
    Range(col & "1:" & col & maxrow).Select
End Sub

操作方法

1. 打开VB界面
   
   

2. 执行宏
   
   
   
   

3. 观察结果
   

具体运行内容请看下面动图