Consulting

Results 1 to 6 of 6

Thread: VBA - what is Chr(1)

  1. #1

    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

    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

  2. #2
    VBAX Expert Leith Ross's Avatar
    Joined
    Oct 2012
    Location
    San Francisco, California
    Posts
    552
    Location
    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.
    Sincerely,
    Leith Ross

    "1N73LL1G3NC3 15 7H3 4B1L17Y 70 4D4P7 70 CH4NG3 - 573PH3N H4WK1NG"

  3. #3
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    7,007
    Location
    Quote Originally Posted by malleshg24 View Post
    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
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  4. #4
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,107
    Location
    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
    I always expect the student to do their homework and find all the errrors I leeve in.

    Please take the time to read the Forum FAQ

  5. #5
    Knowledge Base Approver VBAX Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,903
    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.
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  6. #6
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •