Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / 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,542

    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")

    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:

    Code:
    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,542

    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.



    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

    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.



    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

    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:

    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.

    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.


    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

    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:

    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!

    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