Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 12

Thread: Use of Trim and Proper in VBA

  1. #1
    Join Date
    4th February 2005
    Posts
    71

    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

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    14th July 2004
    Posts
    10,539

    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.
    VB:
    MsgBox Application.WorksheetFunction.Proper("test") 
    MsgBox Application.WorksheetFunction.Trim("Test1                     Test2") 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    4th February 2005
    Posts
    71

    Re: Use of Trim and Proper in VBA

    such as:

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

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    14th July 2004
    Posts
    10,539

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    4th February 2005
    Posts
    71

    Re: Use of Trim and Proper in VBA

    Ouch,

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

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    24th March 2005
    Posts
    507

    Re: Use of Trim and Proper in VBA

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



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

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    24th March 2005
    Posts
    507

    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.



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


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

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    4th February 2005
    Posts
    71

    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:

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

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

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    24th March 2005
    Posts
    507

    Re: Use of Trim and Proper in VBA

    This should be better if you just want Trim.


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

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    4th February 2005
    Posts
    71

    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:

    VB:
     
    =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!

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Trim$ not working in XP
    By Alan j in forum EXCEL HELP
    Replies: 7
    Last Post: March 21st, 2012, 14:09
  2. Trim WorkSheet
    By AFP in forum EXCEL HELP
    Replies: 5
    Last Post: May 12th, 2005, 02:35
  3. trim function
    By Marina in forum EXCEL HELP
    Replies: 3
    Last Post: January 27th, 2005, 17:03
  4. Trim(Right
    By Stapuff in forum EXCEL HELP
    Replies: 2
    Last Post: October 19th, 2004, 02:44
  5. [Solved] TRIM
    By sal21 in forum EXCEL HELP
    Replies: 7
    Last Post: June 20th, 2003, 07:38

Bookmarks

Posting Permissions

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