VBA - what is Chr(1)

Hi Team,

Need your help, in below code what is Chr(1), what string will come in its place if want to replace. Thanks in advance

Code:

---

Dim Arr As Variant
  Arr = Split(Join(Application.Transpose(Sheet1.Range("A2", Cells(Rows.Count, "A").End(xlUp))), Chr(1)) & Chr(1) & _
        Join(Application.Transpose(Sheet2.Range("C2", Cells(Rows.Count, "C").End(xlUp))), Chr(1)) & Chr(1) & _
        Join(Application.Transpose(Sheet3.Range("E2", Cells(Rows.Count, "E").End(xlUp))), Chr(1)), Chr(1))

---

Regards,
mg

Hello malleshg24,

Having a reference to what the string is being used for would be helpful. In the original ASCII table character 1 represented the start of a header.

I suspect a typo here. Chr(10) or line feed would make more sense.

Quote:

---

Originally Posted by malleshg24

Hi Team,

Need your help, in below code what is Chr(1), what string will come in its place if want to replace. Thanks in advance

Dim Arr As Variant
Arr = Split(Join(Application.Transpose(Sheet1.Range("A2", Cells(Rows.Count, "A").End(xlUp))), Chr(1)) & Chr(1) & _
Join(Application.Transpose(Sheet2.Range("C2", Cells(Rows.Count, "C").End(xlUp))), Chr(1)) & Chr(1) & _
Join(Application.Transpose(Sheet3.Range("E2", Cells(Rows.Count, "E").End(xlUp))), Chr(1)), Chr(1))



Regards,
mg

---

Since there's Joins and Splits there, I suspect that Chr(1) is being used as a special marker character (I've used it like that many times) that won't be in regular text

Simple demo, broken into bite-size pieces

Code:

---

Option Explicit


Sub JoinSplit()
    Dim A1 As Variant, A2 As Variant, A3 As Variant, A0 As Variant
    Dim s1 As String, s2 As String, s3 As String, s0 As String
    Dim i As Long
   
    A1 = Array("A1", "B1", "C1", "D1")
    A2 = Array("A2", "B2", "C2", "D2")
    A3 = Array("A3", "B3", "C3", "D3")


    s1 = Join(A1, Chr(1))
    s2 = Join(A2, Chr(1))
    s3 = Join(A3, Chr(1))


    s0 = s1 & Chr(1) & s2 & Chr(1) & s3


    A0 = Split(s0, Chr(1))
   
    For i = LBound(A0) To UBound(A0)
        Debug.Print i & " -- " & A0(i)
    Next i


End Sub

---

Output -- the Chr(1)'s are not in the array -- I'd leave them alone

Quote:

---

0 -- A1
1 -- B1
2 -- C1
3 -- D1
4 -- A2
5 -- B2
6 -- C2
7 -- D2
8 -- A3
9 -- B3
10 -- C3
11 -- D3

---

Looks to me like the Chr(1)'s are the DELIMITERS for the three JOINs and the SPLIT

Using Chr(1), means that the various strings in the various Ranges will not SPLIT on any keyboard character.

Also, Unicode safe.

Inputs

A	B	C	D	E
A		D049		H-
B		E,.:		I
C		F G		J

OutPut:
ABCD049E,.:F GH-IJ

malleshg24, I tried your code and got an error Method range of object '_Worksheet' failed, which is due to unqualified references to Cells.
This doesn't error:

Code:

---

Arr = Split(Join(Application.Transpose(Sheet1.Range("A2", Sheet1.Cells(Rows.Count, "A").End(xlUp))), Chr(1)) & Chr(1) & _
Join(Application.Transpose(Sheet2.Range("C2", Sheet2.Cells(Rows.Count, "C").End(xlUp))), Chr(1)) & Chr(1) & _
Join(Application.Transpose(Sheet3.Range("E2", Sheet3.Cells(Rows.Count, "E").End(xlUp))), Chr(1)), Chr(1))

---

You can shorten the line with:

Code:

---

Arr = Split(Application.TextJoin(Chr(1), False, _
Sheet1.Range("A2", Sheet1.Cells(Rows.Count, "A").End(xlUp)), _
Sheet2.Range("C2", Sheet2.Cells(Rows.Count, "C").End(xlUp)), _
Sheet3.Range("E2", Sheet3.Cells(Rows.Count, "E").End(xlUp))), Chr(1))

---

with the added bonus of ignoring blank cells by changing False to True.

Hi Team,

Thanks you all for your help and good information on it.
Below code works for me, I take all data into range, and after that filter in array,

Below line works, it filter single string and give result.
filteredArray = VBA.Filter(Application.Transpose(arr), "completed", True)

one more question can we filter two strings in vba.filter this line?....
like :=> Array("completed","In Progress")

Code:

---

Sub Test()
Dim arr As Variant
Dim filteredArray() As String
arr = Split(Join(Application.Transpose(Sheet1.Range("A2", Sheet1.Cells(Rows.Count, "A").End(xlUp))), Chr(1)) & Chr(1) & _
Join(Application.Transpose(Sheet2.Range("C2", Sheet2.Cells(Rows.Count, "C").End(xlUp))), Chr(1)) & Chr(1) & _
Join(Application.Transpose(Sheet3.Range("E2", Sheet3.Cells(Rows.Count, "E").End(xlUp))), Chr(1)), Chr(1))
filteredArray = VBA.Filter(Application.Transpose(arr), "completed", True)
MsgBox UBound(filteredArray) + 1
End Sub

---

Thanks
mg