PDA

View Full Version : VBA - what is Chr(1)



malleshg24
01-18-2020, 06:05 PM
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

Leith Ross
01-18-2020, 06:42 PM
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.

Paul_Hossler
01-18-2020, 07:18 PM
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



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



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

SamT
01-18-2020, 08:20 PM
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

p45cal
01-19-2020, 03:45 AM
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:

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:
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.

malleshg24
01-19-2020, 05:45 AM
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")



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