Announcement

Collapse
No announcement yet.

Use of Trim and Proper in VBA

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Use of Trim and Proper in VBA



    I have a list of names in column D that I need to take out extra spaces and use the Proper format with VBA. Can you use both Trim and Proper together, and does TRim in VBA take out spaces between text and leave in one space as it does in Excel? By the way, there will be a varying amount of rows of information (not exceeding 2000 rows).

    Thanks guys

  • #2
    Re: Use of Trim and Proper in VBA

    Trim in VBA does not act like the TRIM worksheet function. It removes only leading and trailing spaces.

    You can use worksheet functions via VBA.
    Code:
    MsgBox Application.WorksheetFunction.Proper("test")
    MsgBox Application.WorksheetFunction.Trim("Test1                     Test2")
    Boo!

    Comment


    • #3
      Re: Use of Trim and Proper in VBA

      such as:

      Code:
      Sub Test_Trim()
      Dim Rn As Range
      Set Rn = Range("d4:d6")
      Application.WorksheetFunction.Proper Rn
      End Sub

      Comment


      • #4
        Re: Use of Trim and Proper in VBA

        That won't work. Just as on a worksheet Proper only works on 1 cell the same applys in VBA.
        Boo!

        Comment


        • #5
          Re: Use of Trim and Proper in VBA

          Ouch,

          Ok thanks--by the way, this forum is fun to watch and learn!

          Comment


          • #6
            Re: Use of Trim and Proper in VBA

            You could try this code. But I dont know about the proper.



            Code:
            Sub Clean_Trim()
            '// From Help Files:
            '// CLEAN > Removes all nonprintable characters from text.
            '// Use CLEAN on text imported from other applications that
            '// contains characters that may not print with your
            '// operating system.
            '// For example, you can use CLEAN to remove some low-level
            '// computer code that is frequently at the beginning and end
            '// of data files and cannot be printed.
            
            '// TRIM > Removes all spaces from text except for single
            '// spaces between words. Use TRIM on text that you have
            '// received from another application that may
            '// have irregular spacing.
            
            Dim CleanTrimRg As Range
            Dim oCell As Range
            Dim Func As WorksheetFunction
            
            Set Func = Application.WorksheetFunction
            
            On Error Resume Next
            Set CleanTrimRg = Selection.SpecialCells(xlCellTypeConstants, 2)
            If Err Then MsgBox "No data to clean and Trim!": Exit Sub
            
            For Each oCell In CleanTrimRg
                oCell = Func.Clean(Func.Trim(oCell))
            Next
            
            End Sub

            Comment


            • #7
              Re: Use of Trim and Proper in VBA

              Here is the propper vba code. You could combinde the Trim and Propper codes together if you want.



              Code:
              Sub Propper()
                
                Selection.SpecialCells(xlCellTypeConstants, 23).Select
              
                   'This Variable represents the cell you want to change.
                  Dim cell
                  
                    'Find all the cells in the current selection.
                  For Each cell In Selection
                        'This code repeats once for each cell in the selection.
                      cell.Value = Application.WorksheetFunction.Proper(cell.Value)
                  Next
              End Sub
              OR


              Code:
              Sub Clean_Trim()
              '// From Help Files:
              '// CLEAN > Removes all nonprintable characters from text.
              '// Use CLEAN on text imported from other applications that
              '// contains characters that may not print with your
              '// operating system.
              '// For example, you can use CLEAN to remove some low-level
              '// computer code that is frequently at the beginning and end
              '// of data files and cannot be printed.
              
              '// TRIM > Removes all spaces from text except for single
              '// spaces between words. Use TRIM on text that you have
              '// received from another application that may
              '// have irregular spacing.
              
              Dim CleanTrimRg As Range
              Dim oCell As Range
              Dim Func As WorksheetFunction
              
              Set Func = Application.WorksheetFunction
              
              On Error Resume Next
              Set CleanTrimRg = Selection.SpecialCells(xlCellTypeConstants, 2)
              If Err Then MsgBox "No data to clean and Trim!": Exit Sub
              
              For Each oCell In CleanTrimRg
                  oCell = Func.Clean(Func.Trim(oCell))
              Next
              
              Selection.SpecialCells(xlCellTypeConstants, 23).Select
              
                   'This Variable represents the cell you want to change.
                  Dim cell
                  
                    'Find all the cells in the current selection.
                  For Each cell In Selection
                        'This code repeats once for each cell in the selection.
                      cell.Value = Application.WorksheetFunction.Proper(cell.Value)
                  Next
              
              End Sub

              Comment


              • #8
                Re: Use of Trim and Proper in VBA

                Excellent, it worked, although I changed it a bit as I only want it to work on the range D2:D2000. Here is the code I used (I couldn't figure out how to select a range in the Sub Proper(), but using xlTextValues seemed to work:

                Code:
                Sub Proper()
                     
                    Selection.SpecialCells(xlCellTypeConstants, xlTextValues).Select
                    
                    Dim cell
                     
                     
                    For Each cell In Selection
                        
                        cell.Value = Application.WorksheetFunction.Proper(cell.Value)
                    Next
                End Sub
                and then to trim...

                Code:
                Sub Clean_Trim()
                         
                    Dim CleanTrimRg As Range
                    Dim oCell As Range
                    Dim Func As WorksheetFunction
                     
                    Set Func = Application.WorksheetFunction
                     
                    On Error Resume Next
                    Set CleanTrimRg = Range("D2:d2000")
                    
                    If Err Then MsgBox "No data to clean and Trim!": Exit Sub
                     
                    For Each oCell In CleanTrimRg
                        oCell = Func.Clean(Func.Trim(oCell))
                    Next
                     
                    Selection.SpecialCells(xlCellTypeConstants, 23).Select
                     
                        Dim cell
                     
                    
                    For Each cell In Selection
                            cell.Value = Application.WorksheetFunction.Proper(cell.Value)
                    Next
                     
                End Sub
                Thanks mightily oh great one!

                By the way, I just purchased the VBA help from Ozgrid--maybe it will help my thickhead get this stuff.

                Comment


                • #9
                  Re: Use of Trim and Proper in VBA

                  This should be better if you just want Trim.


                  Code:
                  Sub Clean_Trim() 
                       
                      Dim CleanTrimRg As Range 
                      Dim oCell As Range 
                      Dim Func As WorksheetFunction 
                       
                      Set Func = Application.WorksheetFunction 
                       
                      On Error Resume Next 
                      Set CleanTrimRg = Range("D2:d2000") 
                       
                      If Err Then MsgBox "No data to clean and Trim!": Exit Sub 
                       
                      For Each oCell In CleanTrimRg 
                          oCell = Func.Clean(Func.Trim(oCell)) 
                      Next 
                  End Sub

                  Comment


                  • #10
                    Re: Use of Trim and Proper in VBA

                    I actually want to trim and proper range d2:d2000--how would I include that in the "proper" portion of the function?

                    While you are on a roll...range g2:g2000 has unformatted zip codes. Some are 5 digits, some are 9 digits. I currently use the following formula on the excel worksheet then copy it to a new column:

                    Code:
                    =IF(LEN(G2)>6,TEXT(G2,"#####-####"),TEXT(G2,"#####"))
                    Can this function be included in VBA?

                    I may be awhile before I answer--going home and honeydews await me.

                    Have a great weekend everybody!

                    Comment


                    • #11
                      Re: Use of Trim and Proper in VBA

                      Here is the range you're looking for it will only highlight texts in the cells but the =Len Formula I dont know how to do that in vba I will look into it and if I find something I'll give you a reply.

                      Code:
                      Sub Clean_Trim_Propper()
                      '// From Help Files:
                      '// CLEAN > Removes all nonprintable characters from text.
                      '// Use CLEAN on text imported from other applications that
                      '// contains characters that may not print with your
                      '// operating system.
                      '// For example, you can use CLEAN to remove some low-level
                      '// computer code that is frequently at the beginning and end
                      '// of data files and cannot be printed.
                      
                      '// TRIM > Removes all spaces from text except for single
                      '// spaces between words. Use TRIM on text that you have
                      '// received from another application that may
                      '// have irregular spacing.
                      
                      Dim CleanTrimRg As Range
                      Dim oCell As Range
                      Dim Func As WorksheetFunction
                      
                      Set Func = Application.WorksheetFunction
                      
                      On Error Resume Next
                      Range("D2:D2000").Select
                      Set CleanTrimRg = Selection.SpecialCells(xlCellTypeConstants, 2)
                      If Err Then MsgBox "No data to clean and Trim!": Exit Sub
                      
                      For Each oCell In CleanTrimRg
                          oCell = Func.Clean(Func.Trim(oCell))
                      Next
                      
                      
                          Selection.SpecialCells(xlCellTypeConstants, 2).Select
                         
                      
                           'This Variable represents the cell you want to change.
                          Dim cell
                          
                            'Find all the cells in the current selection.
                          For Each cell In Selection
                                'This code repeats once for each cell in the selection.
                              cell.Value = Application.WorksheetFunction.Proper(cell.Value)
                          Next
                        
                      End Sub

                      Comment


                      • #12


                        Re: Use of Trim and Proper in VBA

                        Fin Fang Foom

                        As far as i know the Len function in VBA works exactly like the worksheet function.
                        Boo!

                        Comment

                        Working...
                        X