# Removing invisible characters from VBA array and dynamic formula



## Spencer500 (Dec 18, 2022)

Hi,

I am building a VBA macro that will run VLOOKUP on user input with the following functionality: 

1.  Takes in user typed or pasted data from sheet "Main"
2.  Ports the data from sheet "Main" to an array. 
3.  Array processing:  Common invisible characters are removed, data is trimmed, data is casted to text to ensure the values to look up and the look-up data set are the same data type to ensure VLOOKUP will work.   
4.  The array then moves the data to sheet "BlankArray", where a VBA-based dynamic VLOOKUP is run on the data. 

The problem is that invisible characters are somehow being created by this process, which is causing the VLOOKUP to return 500 in cell B6 from cell D4 because a match between A6 and C4 was identified by VLOOKUP.  But the respective cells from sheet "Main" from which the data originated are blank. 

When I select cell A6, then hit Delete, the invisible character is then deleted, which then causes VLOOKUP to return #NA in cell B6, which is what should happen if the invisible character were not in cell A6. 

Invisible characters are also in cells C4 and C5.  If I do not delete cell A6, and if I, instead, select C4, then hit delete, VLOOKUP returns 600 in cell B6 from D5 due to the next invisible character match between C5 and A6. 

How can I modify this process to remove the invisible characters from appearing in the cells that should be blank?  Also, are these invisible characters also appended to the cells that have data?  If so, then how can I ensure the invisible characters are not appended to the cells with data?  Do you have any other suggestions for improvements?

*Please find attached workbook screenshot.  

VBA Code:  *


```
Public InitialArray As Variant

Sub Match()

Sheets("Main").Select

Dim i As Long
Dim MaxFinalRow As Long


Y1 = 1
Y2 = 1
i = 1

Dim FinalRowValue As Long
Dim FinalRowKey As Long

FinalRowValue = Cells(Rows.Count, 1).End(xlUp).Row

FinalRowKey = Cells(Rows.Count, 3).End(xlUp).Row

FinalRowValueToReturn = Cells(Rows.Count, 4).End(xlUp).Row


If FinalRowValue > FinalRowKey Then
    MaxFinalRow = FinalRowValue
Else
    MaxFinalRow = FinalRowKey
End If


InitialArray = Range(Cells(3, 1), Cells(MaxFinalRow, 4))

Sheets("BlankArray").Select

Range("A1:F13").Select
Selection.ClearContents

    For i = 1 To (FinalRowValue - 2)
        InitialArray(i, 1) = Replace(InitialArray(i, 1), Chr(160), vbNullString)
        InitialArray(i, 1) = Replace(InitialArray(i, 1), Chr(10), vbNullString)
        InitialArray(i, 1) = Replace(InitialArray(i, 1), Chr(13), vbNullString)
        InitialArray(i, 1) = Replace(InitialArray(i, 1), Chr(26), vbNullString)
        InitialArray(i, 1) = Replace(InitialArray(i, 1), Chr(3), vbNullString)
        InitialArray(i, 1) = Replace(InitialArray(i, 1), Chr(28), vbNullString)
    Next i
   
    For i = 1 To (FinalRowKey - 2)
        InitialArray(i, 3) = Replace(InitialArray(i, 3), Chr(160), vbNullString)
        InitialArray(i, 3) = Replace(InitialArray(i, 3), Chr(10), vbNullString)
        InitialArray(i, 3) = Replace(InitialArray(i, 3), Chr(13), vbNullString)
        InitialArray(i, 3) = Replace(InitialArray(i, 3), Chr(26), vbNullString)
        InitialArray(i, 3) = Replace(InitialArray(i, 3), Chr(3), vbNullString)
        InitialArray(i, 3) = Replace(InitialArray(i, 3), Chr(28), vbNullString)
    Next i

    For i = 1 To (FinalRowValue - 2)
        InitialArray(i, 1) = Trim(InitialArray(i, 1))
    Next i
   
    For i = 1 To (FinalRowKey - 2)
        InitialArray(i, 3) = Trim(InitialArray(i, 3))
    Next i


For i = 1 To (FinalRowValue - 2)
    InitialArray(i, 1) = CStr(InitialArray(i, 1))
Next i


For i = 1 To (FinalRowKey - 2)
    InitialArray(i, 3) = CStr(InitialArray(i, 3))
Next i

Range(Cells(3, 1), Cells(MaxFinalRow, 4)) = InitialArray


    Range("B3").Formula = "=VLOOKUP(RC[-1],R3C3:R" & FinalRowKey & "C4,2,FALSE)"
    Range("B3").Select
    Selection.AutoFill Destination:=Range(Cells(3, 2), Cells(FinalRowValue, 2)), Type:=xlFillDefault

End Sub
```



Thanks.


----------



## rlv01 (Dec 18, 2022)

The first thing you should do is determine what invisible character you are dealing with. 

Book1ABCDEF12Char Code3140014004942#N/A 5005053#N/A 600516 5004800160759005900538#N/A61000<empty  cell>Sheet3Cell FormulasRangeFormulaB3:B8B3=VLOOKUP(A3,$C$3:$D$8,2,FALSE)F3:F8F3=IF(LEN(A3)>0,CODE(LEFT(TRIM(A3),1)),"<empty  cell>")

    (Tip: when posting code, please try to use 'code tags' to format the code as I have done above

How to Post Your VBA Code

  as it makes the code easier to read.)

Also , instead of posting data as a screen graphic, you might try this. 








						XL2BB - Excel Range to BBCode
					

Excel 'mini-sheet' in messages - XL2BB  Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...




					www.mrexcel.com


----------



## Peter_SSs (Dec 18, 2022)

@Spencer500 
Welcome to the MrExcel board!

As requested already by @rlv01, when posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug. My signature block below has more details (same link as provided by rlv01). I have added the tags for you this time. 😊


----------



## Alex Blakenburg (Dec 18, 2022)

If you think you have identified all the characters that you are likely to need replaced, you can clean up the whole Data Area without looping,
See if this gives you the same or better result.
( Ideally you also want to get rid of the Select statements and Explicitly refer to the workhsheets)


```
Sub CleanData()

Dim i As Long
Dim MaxFinalRow As Long
Dim InitialArray
Dim Y1 As Long, Y2 As Long

Y1 = 1
Y2 = 1
i = 1

Dim FinalRowValue As Long
Dim FinalRowKey As Long
Dim FinalRowValueToReturn As Long

FinalRowValue = Cells(Rows.Count, 1).End(xlUp).Row

FinalRowKey = Cells(Rows.Count, 3).End(xlUp).Row

FinalRowValueToReturn = Cells(Rows.Count, 4).End(xlUp).Row

If FinalRowValue > FinalRowKey Then
    MaxFinalRow = FinalRowValue
Else
    MaxFinalRow = FinalRowKey
End If

InitialArray = Range(Cells(3, 1), Cells(MaxFinalRow, 4))

Sheets("BlankArray").Select

Range("A1:F13").Select
Selection.ClearContents
    
    InitialArray = Application.Substitute(InitialArray, Chr(160), vbNullString)
    InitialArray = Application.Substitute(InitialArray, Chr(10), vbNullString)
    InitialArray = Application.Substitute(InitialArray, Chr(13), vbNullString)
    InitialArray = Application.Substitute(InitialArray, Chr(26), vbNullString)
    InitialArray = Application.Substitute(InitialArray, Chr(3), vbNullString)
    InitialArray = Application.Substitute(InitialArray, Chr(28), vbNullString)
    
    InitialArray = Application.Trim(InitialArray)

Range(Cells(3, 1), Cells(MaxFinalRow, 4)) = InitialArray

    Range("B3").Formula = "=VLOOKUP(RC[-1],R3C3:R" & FinalRowKey & "C4,2,FALSE)"
    Range("B3").Select
    Selection.AutoFill Destination:=Range(Cells(3, 2), Cells(FinalRowValue, 2)), Type:=xlFillDefault

End Sub
```


----------



## Spencer500 (Dec 19, 2022)

Hi @rlv01 and @Alex Blakenburg , 

Thank you for your replies.  

Using Office 365, I have modified my workbook according to your recommendations and the invalid characters have not been identified and have not been eliminated in the results. 

--

rlv01:  
I tried using the original version of your formula as well as three variations of it in an attempt to identify the invisible character in the results as follows:  

Match2.xlsmABCDEFGHIJ3140014004949494942#N/A5005050505053#N/A6005151515165004800<empty  cell>#VALUE!#VALUE!#VALUE!75900590053535353BlankArrayCell FormulasRangeFormulaG3:G7G3=IF(LEN(A3)>0,CODE(LEFT(TRIM(A3),1)),"<empty  cell>")H3:H7H3=CODE(LEFT(TRIM(A3),1))I3:I7I3=CODE(TRIM(A3))J3:J7J3=CODE(A3)B3:B7B3=VLOOKUP(A3,$C$3:$D$7,2,FALSE)


As you can see from the above embedded table, your original formula to identify the invisible character and the three variations of it did not return Char code 160 in any of the row 6 columns.  Did you type in "160" in the embedded table in your reply or was it returned by your formula?  

--

Alex Blakenburg: 
Thanks for the tip that I do not need to traverse the array.  

The invisible characters in cells A6, C4, and C5 are still returned in the results after running your CleanData Subroutine.  
The embedded table contains the results after running your CleanData Subroutine. 

In your CleanData Subroutine you used the Substitute function to eliminate the invisible character codes instead of the Replace function as I did in my post.  
What is the difference between using the Replace function vs. using the Substitute function when manipulating data in VBA arrays? 
I noticed that you did not declare InitalArray as a Variant.  What is the advantage of not using the Variant data type for InitialArray in this macro?  

--

Do you have any other suggestions for how to identify the character code(s) for the invisible characters or to eliminate the invisible characters so that they do not appear in the results?  

Thanks.


----------



## Alex Blakenburg (Dec 19, 2022)

Spencer500 said:


> In your CleanData Subroutine you used the Substitute function to eliminate the invisible character codes instead of the Replace function as I did in my post.
> What is the difference between using the Replace function vs. using the Substitute function when manipulating data in VBA arrays?
> I noticed that you did not declare InitalArray as a Variant. What is the advantage of not using the Variant data type for InitialArray in this macro?


• Application.Trim & Application.Substitute will work on the entire array without looping, there are only a few functions that will do that.
• InitialArray - I moved it from a public variable to a module variable and simply forgot to add the "as Variant", as it happens it is the default but I would normally spell it out.

The Clean is not going to do anything if the issue is not one of the specified characters.

Most of the code formulas being tried are assuming that the character is on the left. If you use Len on the field does the length seem consistent with what you are seeing in the cell ?
Try getting the Right character and seeing what the code is on that.


----------



## Spencer500 (Dec 19, 2022)

Hi Alex, 

Thanks for pointing out those useful features of the Trim and Substitute functions.  

I have modified the formulas to take the character from the right per your recommendation (see highlighted columns on far right).  But the results are the same as those returned from the other formulas. Grid from modified workbook:  

Match3.xlsmABCDEFGHIJKL31400140049494949494942#N/A50050505050505053#N/A60051515151515165004800<empty  cell>#VALUE!#VALUE!#VALUE!<empty  cell>#VALUE!759005900535353535353BlankArrayCell FormulasRangeFormulaG3:G7G3=IF(LEN(A3)>0,CODE(LEFT(TRIM(A3),1)),"<empty  cell>")H3:H7H3=CODE(LEFT(TRIM(A3),1))I3:I7I3=CODE(TRIM(A3))J3:J7J3=CODE(A3)K3:K7K3=IF(LEN(A3)>0,CODE(RIGHT(TRIM(A3),1)),"<empty  cell>")L3:L7L3=CODE(RIGHT(TRIM(A3),1))B3:B7B3=VLOOKUP(A3,$C$3:$D$7,2,FALSE)

Do you have any other recommendations for how to identify the character code for the invisible characters?  
If it is not possible to use VBA or formulas to identify the character code for some invisible characters, then do you have any educated guesses on what the character code for the invisible character could be so that it can be removed by the array-based data cleansing process?  

Thanks.


----------



## Alex Blakenburg (Dec 19, 2022)

I am probably running out of ideas. Can you try adding this to the end of your code and see if it makes any difference.


```
Dim rng As Range
    Set rng = Range(Cells(3, 1), Cells(FinalRowValue, 4)).Columns(1)
    rng.TextToColumns Destination:=rng.Cells(1, 1), DataType:=xlDelimited, _
                TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
                Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
                :=Array(1, 1), TrailingMinusNumbers:=True
                
    Set rng = Range(Cells(3, 1), Cells(FinalRowValue, 4)).Columns(3)
    rng.TextToColumns Destination:=rng.Cells(1, 1), DataType:=xlDelimited, _
                TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
                Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
                :=Array(1, 1), TrailingMinusNumbers:=True
```


----------



## rlv01 (Dec 19, 2022)

Match3.xlsmABCDEFGHIJKL31400140049494949494942#N/A50050505050505053#N/A60051515151515165004800<empty  cell>#VALUE!#VALUE!#VALUE!<empty  cell>#VALUE!759005900535353535353BlankArrayCell FormulasRangeFormulaG3:G7G3=IF(LEN(A3)>0,CODE(LEFT(TRIM(A3),1)),"<empty  cell>")H3:H7H3=CODE(LEFT(TRIM(A3),1))I3:I7I3=CODE(TRIM(A3))J3:J7J3=CODE(A3)K3:K7K3=IF(LEN(A3)>0,CODE(RIGHT(TRIM(A3),1)),"<empty  cell>")L3:L7L3=CODE(RIGHT(TRIM(A3),1))B3:B7B3=VLOOKUP(A3,$C$3:$D$7,2,FALSE)

The formula in cell G6 (above) is telling you that there are no hidden characters in cell A6. If there were, `=Len(A6)` would return a value greater than zero. It appears that VLookup is returning 500 because A6 is empty and C4 it is the first empty cell in the lookup range. To confirm, put a value (any value) in cell C4 (above) and see if B6 changes to 600. Not sure why it is doing that. Vlookup does not behave that way for me, but it does suggest a workaround strategy of including a cell length test as part of the formula: 

Book1ABCDEFG12Lookup Char CodeTable Char Code314001400494942#N/A50050<empty  cell>53#N/A60051<empty  cell>6#N/A4800<empty  cell>527590059005353Sheet1Cell FormulasRangeFormulaF3:F7F3=IF(LEN(A3)>0,CODE(LEFT(TRIM(A3),1)),"<empty  cell>")G3:G7G3=IF(LEN(C3)>0,CODE(LEFT(TRIM(C3),1)),"<empty  cell>")B3:B7B3=IF(LEN(A3)=0,"#N/A",(VLOOKUP(A3,$C$3:$D$7,2,FALSE)))


----------



## Peter_SSs (Dec 19, 2022)

rlv01 said:


> The formula in cell G6 (above) is telling you that there are no hidden characters in cell A6.


.. unless it is a zero-length string.

I suspect that cells A6, C4 & C5 originally contained formulas that returned null strings ""
Subsequently most likely a copy/paste values has occurred. This still leaves those cells with a zero length string.
See my sheet below & results in A9:A10 (& the VLOOKUP in B6)

Spencer500.xlsmABCD1231400140042#N/A50053#N/A6006500480075900590089010TRUEMainCell FormulasRangeFormulaB3:B7B3=VLOOKUP(A3,$C$3:$D$7,2,FALSE)A9A9=LEN(A6)A10A10=ISTEXT(A6)

With that sheet active, try running this code.

```
Sub test()
  With Range("A3:A7")
    .Value = .Value
  End With
  With Range("C3:C7")
    .Value = .Value
  End With
End Sub
```

Having done that my sheet now looks like this. Note changes to A10 and B6.

Spencer500.xlsmABCD1231400140042#N/A50053#N/A6006#N/A480075900590089010FALSEMainCell FormulasRangeFormulaB3:B7B3=VLOOKUP(A3,$C$3:$D$7,2,FALSE)A9A9=LEN(A6)A10A10=ISTEXT(A6)


----------



## Spencer500 (Dec 18, 2022)

Hi,

I am building a VBA macro that will run VLOOKUP on user input with the following functionality: 

1.  Takes in user typed or pasted data from sheet "Main"
2.  Ports the data from sheet "Main" to an array. 
3.  Array processing:  Common invisible characters are removed, data is trimmed, data is casted to text to ensure the values to look up and the look-up data set are the same data type to ensure VLOOKUP will work.   
4.  The array then moves the data to sheet "BlankArray", where a VBA-based dynamic VLOOKUP is run on the data. 

The problem is that invisible characters are somehow being created by this process, which is causing the VLOOKUP to return 500 in cell B6 from cell D4 because a match between A6 and C4 was identified by VLOOKUP.  But the respective cells from sheet "Main" from which the data originated are blank. 

When I select cell A6, then hit Delete, the invisible character is then deleted, which then causes VLOOKUP to return #NA in cell B6, which is what should happen if the invisible character were not in cell A6. 

Invisible characters are also in cells C4 and C5.  If I do not delete cell A6, and if I, instead, select C4, then hit delete, VLOOKUP returns 600 in cell B6 from D5 due to the next invisible character match between C5 and A6. 

How can I modify this process to remove the invisible characters from appearing in the cells that should be blank?  Also, are these invisible characters also appended to the cells that have data?  If so, then how can I ensure the invisible characters are not appended to the cells with data?  Do you have any other suggestions for improvements?

*Please find attached workbook screenshot.  

VBA Code:  *


```
Public InitialArray As Variant

Sub Match()

Sheets("Main").Select

Dim i As Long
Dim MaxFinalRow As Long


Y1 = 1
Y2 = 1
i = 1

Dim FinalRowValue As Long
Dim FinalRowKey As Long

FinalRowValue = Cells(Rows.Count, 1).End(xlUp).Row

FinalRowKey = Cells(Rows.Count, 3).End(xlUp).Row

FinalRowValueToReturn = Cells(Rows.Count, 4).End(xlUp).Row


If FinalRowValue > FinalRowKey Then
    MaxFinalRow = FinalRowValue
Else
    MaxFinalRow = FinalRowKey
End If


InitialArray = Range(Cells(3, 1), Cells(MaxFinalRow, 4))

Sheets("BlankArray").Select

Range("A1:F13").Select
Selection.ClearContents

    For i = 1 To (FinalRowValue - 2)
        InitialArray(i, 1) = Replace(InitialArray(i, 1), Chr(160), vbNullString)
        InitialArray(i, 1) = Replace(InitialArray(i, 1), Chr(10), vbNullString)
        InitialArray(i, 1) = Replace(InitialArray(i, 1), Chr(13), vbNullString)
        InitialArray(i, 1) = Replace(InitialArray(i, 1), Chr(26), vbNullString)
        InitialArray(i, 1) = Replace(InitialArray(i, 1), Chr(3), vbNullString)
        InitialArray(i, 1) = Replace(InitialArray(i, 1), Chr(28), vbNullString)
    Next i
   
    For i = 1 To (FinalRowKey - 2)
        InitialArray(i, 3) = Replace(InitialArray(i, 3), Chr(160), vbNullString)
        InitialArray(i, 3) = Replace(InitialArray(i, 3), Chr(10), vbNullString)
        InitialArray(i, 3) = Replace(InitialArray(i, 3), Chr(13), vbNullString)
        InitialArray(i, 3) = Replace(InitialArray(i, 3), Chr(26), vbNullString)
        InitialArray(i, 3) = Replace(InitialArray(i, 3), Chr(3), vbNullString)
        InitialArray(i, 3) = Replace(InitialArray(i, 3), Chr(28), vbNullString)
    Next i

    For i = 1 To (FinalRowValue - 2)
        InitialArray(i, 1) = Trim(InitialArray(i, 1))
    Next i
   
    For i = 1 To (FinalRowKey - 2)
        InitialArray(i, 3) = Trim(InitialArray(i, 3))
    Next i


For i = 1 To (FinalRowValue - 2)
    InitialArray(i, 1) = CStr(InitialArray(i, 1))
Next i


For i = 1 To (FinalRowKey - 2)
    InitialArray(i, 3) = CStr(InitialArray(i, 3))
Next i

Range(Cells(3, 1), Cells(MaxFinalRow, 4)) = InitialArray


    Range("B3").Formula = "=VLOOKUP(RC[-1],R3C3:R" & FinalRowKey & "C4,2,FALSE)"
    Range("B3").Select
    Selection.AutoFill Destination:=Range(Cells(3, 2), Cells(FinalRowValue, 2)), Type:=xlFillDefault

End Sub
```



Thanks.


----------



## Spencer500 (Dec 19, 2022)

Hi  @Alex Blakenburg ,

Thank you for your time and expertise.  From a results standpoint, your solution fixed the issue.  But I am still interested in learning what caused this issue.  

To reiterate from my initial post, cells A6 and C4 were visibly blank, and deleting the contents of cell A6 caused B6 to return #NA instead of 500, which appeared to indicate that there was an invisible character in cell A6 that caused it to match with the same invisible character in cell C4 to return 500.  But the formulas provided by rlv01 appeared to indicate that there were no invisible characters in cell A6.  

My ideal objective for this post was to identify a change to the array-based data cleansing process to prevent the apparent invisible characters from appearing in the results instead of removing the apparent invisible characters from the results.  However, thus far that may not be practical because your suggested VBA code modifications still returned the apparent invisible characters that caused VLOOKUP to not function as expected because VLOOKUP with the "FALSE" exact match parameter that I used does not match blank values.  It is my understanding that when using the "FALSE" exact match parameter VLOOKUP returns #NA if a match is not found for the value that is looked up or if the value that is looked up is blank.  

So, what do you think caused the apparent invisible characters to appear in the results?  Also, why did running Text To Columns on the results cause VLOOKUP to function as expected? 

--

Hi @rlv01 , 

Per your request I, when using the version of my workbook that does not include Alex's Text To Columns VBA code, I entered "a" in cell C4.  This causes VLOOKUP to return 600, which is what I would expect as noted in my interpretation of this observation from my original post as follows:  

"When I select cell A6, then hit Delete, the invisible character is then deleted, which then causes VLOOKUP to return #NA in cell B6, which is what should happen if the invisible character were not in cell A6.

Invisible characters are also in cells C4 and C5. If I do not delete cell A6, and if I, instead, select C4, then hit delete, VLOOKUP returns 600 in cell B6 from D5 due to the next invisible character match between C5 and A6."

Table:  
Match3.xlsmABCD31400140042#N/Aa50053#N/A60066004800759005900BlankArrayCell FormulasRangeFormulaB3:B7B3=VLOOKUP(A3,$C$3:$D$7,2,FALSE)

As noted from my response to Alex, my original objective for this post was to identify a change to the array-based data cleansing process to prevent the apparent invisible characters from appearing in the results instead of removing the apparent invisible characters from the results.  Your solution does work.  But it does not prevent the apparent invisible characters from appearing in the results from the VBA processing, which is my objective.  Your solution could work as follows:  

Match3.xlsmABCD31400140042#N/Aa50053#N/A6006 4800759005900BlankArrayCell FormulasRangeFormulaB3:B7B3=IF(LEN(A3)>0, VLOOKUP(A3,$C$3:$D$7,2,FALSE), "")

--
Hi @Peter_SSs ,

I added the VBA from your test Subroutine at the end of the original version of my Subroutine.  After running the modified Subroutine, the results are as follows:  

Match5.xlsmABCD31400140042#N/A50053#N/A60065004800759005900BlankArrayCell FormulasRangeFormulaB3:B7B3=VLOOKUP(A3,$C$3:$D$7,2,FALSE)

As you can see 500 is still returned by B6, which does not match the results you provided after your comment, "Having done that my sheet now looks like this. Note changes to A10 and B6.".  

Then after I delete the contents of cell A6 the results are as follows:  

Match5.xlsmABCD31400140042#N/A50053#N/A6006#N/A4800759005900BlankArrayCell FormulasRangeFormulaB3:B7B3=VLOOKUP(A3,$C$3:$D$7,2,FALSE)

As you can see #NA is now returned in B6 as expected because the apparent invisible character that caused A6 to match with C4 was deleted from A6.  

In response to your comment:  

"I suspect that cells A6, C4 & C5 originally contained formulas that returned null strings ""
Subsequently most likely a copy/paste values has occurred. This still leaves those cells with a zero length string."

Cells A6, C4 & C5 did not originally contain formulas.  The values from columns A, C, and D in the final results sheet originally came from another sheet and were not formulas on that sheet.  But these values were pasted into that sheet.  As noted from the original VBA code that I submitted in my first post in this thread, the objective of my macro is to perform array-based processing from user inputted data from one sheet then run a VBA-based dynamic VLOOKUP on that array-processed data on a different sheet.  The purpose of the array-based processing is remove common invisible characters, trim the data, and cast the column containing the values that are looked up and the column containing the look-up values both as text to ensure VLOOKUP does not fail due to comparing the same values that could initially be different data types such as 1, which could be a Number or String on the sheet where the user pastes the data.   

If it is possible, my objective for this post is to make an improvement to the array-based processing on the user inputted data to prevent these apparent invisible characters from appearing in the results.  Do you see any issues with Alex's CleanData Subroutine that are causing these apparent invisible characters from appearing in the results?  Alex's CleanData Subroutine made improvements to my original VBA code.  But the CleanData Subroutine did not prevent the apparent invisible characters from appearing in the results.  

--

Thanks.


----------



## Alex Blakenburg (Dec 20, 2022)

Great feedback.
Is there anyway you *could share your sheet with the invisible characters *via Drop box, Google Drive or some other sharing platform ?
At this point the only zero-length strings I can get that could in principle cause the issue is a single quote ( ' )f or a formula result ie ="". 
However passing it through an array and outputting the array to BlankArray converted both of these to Empty / Blank so didn't cause the issue you are experiencing.

Just to cover off all basis, if you click in Main A6, I don't suppose you can see a single quote mark (or anything else) in the formula bar do you ?
Also unlikely but if in you stop the code straight after loading IntialArray do you just see the double quotes in (2,1)
ie




The text to columns does some conversion in the background and seems to be able to clean it up.


----------



## Peter_SSs (Dec 20, 2022)

Like Alex, to investigate further I would like to see an actual file (with original data) and including your existing vba code.


----------



## Spencer500 (Dec 20, 2022)

Hi  @Alex Blakenburg ,

When I click on Main A6, I do not see a single quote mark.  The formula bar is blank.  

I do not use the Watch Window.  Consequently, I cannot answer your question regarding the double quotes.  

The improved VBA code that you provided is in Subroutine CleanData.  

It seems that porting data from a worksheet range that contains blank cells to a VBA array results in the creation of an invisible character in the respective array addresses of the blank cells.  If blank values can be identified in the array by reporting string lengths of 0, then we could loop through the array to identify addresses of the array that store values that contain string lengths equal to 0 and replace the contents of these addresses with vbNullString or something similar that would not result in carrying an invisible character through to the final output in the destination sheet, which in this workbook is BlankArray.  

I tried this in Subroutine CleanData2.  In CleanData2, notice that I added two array traversals to perform what I noted above.  But this did not work.  

Per your request, the file can be downloaded at this link and will only be available for seven days:  






						Download
					

Click to download the files for this transfer with TransferNow




					www.transfernow.net
				




Please let me know if we can modify the array data transfer and cleansing process to ensure the array does not store values that will result in invisible characters carried through to the destination worksheet when the array contents is ported to the destination worksheet.  

Thanks.


----------



## Peter_SSs (Dec 20, 2022)

Spencer500 said:


> Per your request, the file can be downloaded at this link and will only be available for seven days:


Looking at the the Sheet 'BlankArray' the format of columns A & C is *Text*. Your replacements are replacing various characters with null strings and then placing them in a Text cell so they will still be null strings.

Try adding this blue line into Sub CleanData() above the line shown


```
*Range(Cells(3, 1), Cells(MaxFinalRow, 4)).NumberFormat = "General"*
Range(Cells(3, 1), Cells(MaxFinalRow, 4)) = InitialArray
```


----------



## Spencer500 (Dec 20, 2022)

Hi Peter, 

Thanks for this suggestion.  But this will not work because the value to look up (Sheet BlankArray column A) and the lookup identifier (Sheet BlankArray column C) must be text because it possible that the value to look up and the lookup identifier on the user input worksheet Main could have leading zeros.  When using your modification, those leading zeros are dropped and cause an invalid match between cell A3 and C3 resulting in 11 being returned by VLOOKUP in cell B3.  In this example, the A3 user input value in Sheet Main = 0001 and the C3 user input value = 0011, which are different text values and do not match.   

User input on Sheet Main:  

Match_Test2_1.xlsmABCD3000100100114000200020012500030003001360004000400147000500050015Main


Destination sheet BlankArray:  

Match_Test2_1.xlsmABCD31111114212212531331364144147515515BlankArrayCell FormulasRangeFormulaB3:B7B3=VLOOKUP(A3,$C$3:$D$7,2,FALSE)

If I understand your point correctly, if a null string value in the array is causing the destination cell to not be a true blank, then what should be used in place of vbNullString in the following Substitute statement:  

InitialArray = Application.Substitute(InitialArray, Chr(160), vbNullString)

But in the case of the specific data in the worksheet I provided in the link, no array code should be setting any of the array addresses to vbNullString in Subroutine CleanData.  To reiterate my point from last post, I think the invisible character that is causing visibly blank cells to not be true blanks is created in the array when an array address is set equal to a blank worksheet cell.  But since the initial user input worksheet and the destination worksheet "value to look up" (column A) and "the lookup identifier" (column C) must be text to ensure leading zeros are not dropped, do you have an alternative solution?  

I want to say also, that I really appreciate your time to assist with this question.  With that said, I want to reiterate that if there is no practical solution available to ensure the blanks in the destination sheet are true blanks by changing the array process, then it may be best to end this thread and consider Alex's Text To Columns solution the most practical answer.  

Please let me know your thoughts.  

Thanks.


----------



## Peter_SSs (Dec 20, 2022)

Would the simplest solution be to amend the column B formula?

```
Range("B3").Formula = "=IF(RC[-1]="""",NA(),VLOOKUP(RC[-1],R3C3:R" & FinalRowKey & "C4,2,FALSE))"
```

BTW, you can enter the whole column of formulas at once rather than taking those 3 lines of code to do it.

```
Range("B3:B" & FinalRowValue).Formula = "=IF(RC[-1]="""",NA(),VLOOKUP(RC[-1],R3C3:R" & FinalRowKey & "C4,2,FALSE))"
```


----------



## Spencer500 (Dec 20, 2022)

Hi Peter, 

Yes, this solution results in VLOOKUP returning expected results and is similar to the solution suggested by rlv01. 

And thanks for the tip that this can be done using one line of code.  Is there any performance difference between the single line dynamic formula solution vs. using Selection.AutoFill Destination?   

I have an additional question.  

As noted earlier, part of the array data cleansing process is removing common invisible characters and trimming the values.  But I also cast "value to look up" (column A) and "the lookup identifier" (column C) to text to ensure VLOOKUP does not fail due to comparing different data types.  So is it better to run the cast statement before or after the invisible character removal and trim code?  

--

Invisible Character Removal and value trimming before casting:  


```
InitialArray = Application.Substitute(InitialArray, Chr(160), vbNullString)
    InitialArray = Application.Substitute(InitialArray, Chr(10), vbNullString)
    InitialArray = Application.Substitute(InitialArray, Chr(13), vbNullString)
    InitialArray = Application.Substitute(InitialArray, Chr(26), vbNullString)
    InitialArray = Application.Substitute(InitialArray, Chr(3), vbNullString)
    InitialArray = Application.Substitute(InitialArray, Chr(28), vbNullString)
    
    InitialArray = Application.Trim(InitialArray)


For i = 1 To (FinalRowValue - 2)
    InitialArray(i, 1) = CStr(InitialArray(i, 1))
Next i


For i = 1 To (FinalRowKey - 2)
    InitialArray(i, 3) = CStr(InitialArray(i, 3))
Next i
```

--

Invisible Character Removal and value trimming after casting:  


```
For i = 1 To (FinalRowValue - 2)
    InitialArray(i, 1) = CStr(InitialArray(i, 1))
Next i


For i = 1 To (FinalRowKey - 2)
    InitialArray(i, 3) = CStr(InitialArray(i, 3))
Next i
    
    InitialArray = Application.Substitute(InitialArray, Chr(160), vbNullString)
    InitialArray = Application.Substitute(InitialArray, Chr(10), vbNullString)
    InitialArray = Application.Substitute(InitialArray, Chr(13), vbNullString)
    InitialArray = Application.Substitute(InitialArray, Chr(26), vbNullString)
    InitialArray = Application.Substitute(InitialArray, Chr(3), vbNullString)
    InitialArray = Application.Substitute(InitialArray, Chr(28), vbNullString)
    
    InitialArray = Application.Trim(InitialArray)
```

--

Is it ok if I "Mark as Solution" more than one post in this thread?  

Thanks.


----------



## Peter_SSs (Dec 20, 2022)

Spencer500 said:


> Is it ok if I "Mark as Solution" more than one post in this thread?


You can't.

In relation to your other questions you are probably best to set up some larger dummy sample data and do some tests yourself.


----------



## Alex Blakenburg (Dec 20, 2022)

If you really want to follow your original concept through, then leave columns 1 & 3 formatted as text and replace this line:

```
Range(Cells(3, 1), Cells(MaxFinalRow, 4)) = InitialArray
```

With this:

```
Dim OutputArray() As Variant
ReDim OutputArray(1 To UBound(InitialArray, 1), 1 To UBound(InitialArray, 2))
Dim j As Long

For i = 1 To UBound(InitialArray, 1)
    For j = 1 To UBound(InitialArray, 2)
        If InitialArray(i, j) <> vbNullString Then
            OutputArray(i, j) = InitialArray(i, j)
        End If
    Next j
Next i

Range(Cells(3, 1), Cells(MaxFinalRow, 4)) = OutputArray
```

What it does is this:


----------



## Spencer500 (Dec 18, 2022)

Hi,

I am building a VBA macro that will run VLOOKUP on user input with the following functionality: 

1.  Takes in user typed or pasted data from sheet "Main"
2.  Ports the data from sheet "Main" to an array. 
3.  Array processing:  Common invisible characters are removed, data is trimmed, data is casted to text to ensure the values to look up and the look-up data set are the same data type to ensure VLOOKUP will work.   
4.  The array then moves the data to sheet "BlankArray", where a VBA-based dynamic VLOOKUP is run on the data. 

The problem is that invisible characters are somehow being created by this process, which is causing the VLOOKUP to return 500 in cell B6 from cell D4 because a match between A6 and C4 was identified by VLOOKUP.  But the respective cells from sheet "Main" from which the data originated are blank. 

When I select cell A6, then hit Delete, the invisible character is then deleted, which then causes VLOOKUP to return #NA in cell B6, which is what should happen if the invisible character were not in cell A6. 

Invisible characters are also in cells C4 and C5.  If I do not delete cell A6, and if I, instead, select C4, then hit delete, VLOOKUP returns 600 in cell B6 from D5 due to the next invisible character match between C5 and A6. 

How can I modify this process to remove the invisible characters from appearing in the cells that should be blank?  Also, are these invisible characters also appended to the cells that have data?  If so, then how can I ensure the invisible characters are not appended to the cells with data?  Do you have any other suggestions for improvements?

*Please find attached workbook screenshot.  

VBA Code:  *


```
Public InitialArray As Variant

Sub Match()

Sheets("Main").Select

Dim i As Long
Dim MaxFinalRow As Long


Y1 = 1
Y2 = 1
i = 1

Dim FinalRowValue As Long
Dim FinalRowKey As Long

FinalRowValue = Cells(Rows.Count, 1).End(xlUp).Row

FinalRowKey = Cells(Rows.Count, 3).End(xlUp).Row

FinalRowValueToReturn = Cells(Rows.Count, 4).End(xlUp).Row


If FinalRowValue > FinalRowKey Then
    MaxFinalRow = FinalRowValue
Else
    MaxFinalRow = FinalRowKey
End If


InitialArray = Range(Cells(3, 1), Cells(MaxFinalRow, 4))

Sheets("BlankArray").Select

Range("A1:F13").Select
Selection.ClearContents

    For i = 1 To (FinalRowValue - 2)
        InitialArray(i, 1) = Replace(InitialArray(i, 1), Chr(160), vbNullString)
        InitialArray(i, 1) = Replace(InitialArray(i, 1), Chr(10), vbNullString)
        InitialArray(i, 1) = Replace(InitialArray(i, 1), Chr(13), vbNullString)
        InitialArray(i, 1) = Replace(InitialArray(i, 1), Chr(26), vbNullString)
        InitialArray(i, 1) = Replace(InitialArray(i, 1), Chr(3), vbNullString)
        InitialArray(i, 1) = Replace(InitialArray(i, 1), Chr(28), vbNullString)
    Next i
   
    For i = 1 To (FinalRowKey - 2)
        InitialArray(i, 3) = Replace(InitialArray(i, 3), Chr(160), vbNullString)
        InitialArray(i, 3) = Replace(InitialArray(i, 3), Chr(10), vbNullString)
        InitialArray(i, 3) = Replace(InitialArray(i, 3), Chr(13), vbNullString)
        InitialArray(i, 3) = Replace(InitialArray(i, 3), Chr(26), vbNullString)
        InitialArray(i, 3) = Replace(InitialArray(i, 3), Chr(3), vbNullString)
        InitialArray(i, 3) = Replace(InitialArray(i, 3), Chr(28), vbNullString)
    Next i

    For i = 1 To (FinalRowValue - 2)
        InitialArray(i, 1) = Trim(InitialArray(i, 1))
    Next i
   
    For i = 1 To (FinalRowKey - 2)
        InitialArray(i, 3) = Trim(InitialArray(i, 3))
    Next i


For i = 1 To (FinalRowValue - 2)
    InitialArray(i, 1) = CStr(InitialArray(i, 1))
Next i


For i = 1 To (FinalRowKey - 2)
    InitialArray(i, 3) = CStr(InitialArray(i, 3))
Next i

Range(Cells(3, 1), Cells(MaxFinalRow, 4)) = InitialArray


    Range("B3").Formula = "=VLOOKUP(RC[-1],R3C3:R" & FinalRowKey & "C4,2,FALSE)"
    Range("B3").Select
    Selection.AutoFill Destination:=Range(Cells(3, 2), Cells(FinalRowValue, 2)), Type:=xlFillDefault

End Sub
```



Thanks.


----------



## Alex Blakenburg (Dec 20, 2022)

Spencer500 said:


> So is it better to run the cast statement before or after the invisible character removal and trim code?



I don't believe your "casting" is serving any purpose.  I am not using it my code and I am getting the same results.
If you look in the picture above the 800 is numeric in my sheet main but it has quotes around it in the array in the watch window and is described as a Variant/String.
Regardless of your CStr if you write it out from the Array to a cell formatted General or Number it will convert back to a number.


----------



## Spencer500 (Dec 20, 2022)

Hi Alex, 

Your solution results in VLOOKUP to function as expected.  

I think this is the best solution because it is array-based.  

I did some more testing on this and I think you're right about the array casting step not being necessary.  But what appears to continue to be necessary is for columns A and C in the destination sheet to be Text.  This ensures that 1 (Number on input sheet) will not be matched with 001 (Text on input sheet).  

Thanks.


----------



## Alex Blakenburg (Dec 20, 2022)

If your numbers aren't numbers that you want to run calculations on but Identification numbers with leading zeroes then you may as well format those columns as text.
You can incorporate that into your code.
You would need to do that "before" you write the data to the sheet.


```
Columns(1).NumberFormat = "@"
Columns(3).NumberFormat = "@"
```


----------



## Spencer500 (Dec 20, 2022)

Yes true.  

Thanks.


----------

