EXcel VBA Trim

JEames

New Member
Joined
Nov 17, 2014
Messages
12
Hi, I'm trying to Trim visible cells after a filter is applied. It all works except for the result.

Current Cell Value: "11 05 18 09:06:50: closed by linker #MIT # Updates were made well before SLA expired"
Result Required Cell Value: "#MIT # Updates were made well before SLA expired"
Result with Present Code: "MIT"

Code:
Sub Step9a()


    ActiveSheet.Range("$A$1:$BG$204").AutoFilter Field:=31, Criteria1:="<>#*", _
        Operator:=xlAnd
        
    Range("AE1").Offset(1, 0).Select
    Range(Selection, Selection.End(xlDown)).SpecialCells(xlCellTypeVisible).Select
    Dim c As Range
    
    For Each c In Selection
    If c.Value <> "" Then c.Value = Trim(Split(c, "#")(1))
    Next
    
    ActiveSheet.ShowAllData
    SendKeys "{ESC}"
End Sub
 
Last edited:

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Try this.
Code:
Sub Step9a()


    ActiveSheet.Range("$A$1:$BG$204").AutoFilter Field:=31, Criteria1:="<>#*", _
        Operator:=xlAnd
        
    Range("AE1").Offset(1, 0).Select
    Range(Selection, Selection.End(xlDown)).SpecialCells(xlCellTypeVisible).Select
    Dim c As Range
    
    For Each c In Selection
        If c.Value <> "" Then c.Value = Trim(Mid(c.Value, InStr(c.Value, "#"))
    Next
    
    ActiveSheet.ShowAllData
    SendKeys "{ESC}"
End Sub
 
Last edited:
Upvote 0
Another option
Code:
If c.Value <> "" Then c.Value = "#" & Trim(Split(c, "#")(1)) & "#"
 
Upvote 0
I think your problem is that the "split" sees both # and breaks the text into multiple elements, you'd be better off looking for the first occurrence of # with InStr and then using a combination of Right & Len:

c.Value = Right(c.Value, Len(c.Value) - InStr(c.value, "#") + 1)
 
Upvote 0
Thank you so much, this works perfectly, except your missing a closing bracket at the end of "If" :)

Try this.
Code:
Sub Step9a()


    ActiveSheet.Range("$A$1:$BG$204").AutoFilter Field:=31, Criteria1:="<>#*", _
        Operator:=xlAnd
        
    Range("AE1").Offset(1, 0).Select
    Range(Selection, Selection.End(xlDown)).SpecialCells(xlCellTypeVisible).Select
    Dim c As Range
    
    For Each c In Selection
        If c.Value <> "" Then c.Value = Trim(Mid(c.Value, InStr(c.Value, "#"))
    Next
    
    ActiveSheet.ShowAllData
    SendKeys "{ESC}"
End Sub
 
Upvote 0
All my life (minor exaggeration) I have been working out the remaining length of a field when using Mid .... I never realised you only had to specify where it starts .... what a time saver :cool:
 
Upvote 0

Forum statistics

Threads
1,223,900
Messages
6,175,276
Members
452,629
Latest member
SahilPolekar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top