Create sparse matrix using vbscript

Question

Suppose I have Array = {A, B, Y, X}. Now I have an Excel worksheet that can have a dynamic number of columns and rows. An example in the following example:

Input:

ColA ColB ColC .... TPY .... CYD .... BAM .... ZRX .... 

Output:

 ColA ColB ColC .... - - Y .... - Y - .... BA - .... - - X .... 

If all columns will have only array values, if any other values ​​are found, they should be replaced with "-"

Is there any faster process to do this using VBscript, except for the relatively slow looping method?

Thanks,

Answer 1

 Sub Macro1() Dim arr, i, rng As Range arr = Array("X", "Y", "Z") Set rng = ActiveSheet.Range("A1").CurrentRegion With Application .ScreenUpdating = False .Calculation = xlCalculationManual End With For i = LBound(arr) To UBound(arr) rng.Replace What:=arr(i), Replacement:="-", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _ ReplaceFormat:=False Next i With Application .ScreenUpdating = True .Calculation = xlCalculationAutomatic End With End Sub 

EDIT:

 Sub KeepValues() Dim arr, arrVals, i, rng As Range, r, c Dim keepval As Boolean arr = Array("X", "Y", "Z") Set rng = ActiveSheet.Range("A1").CurrentRegion arrVals = rng.Value For r = 1 To UBound(arrVals, 1) For c = 1 To UBound(arrVals, 2) keepval = False For i = LBound(arr) To UBound(arr) If arr(i) = arrVals(r, c) Then keepval = True Exit For End If Next i If Not keepval Then arrVals(r, c) = "" Next c Next r rng.Value = arrVals End Sub