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
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.