EXcel VBA Trim

[JEames]

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"

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

 

[Norie]

Try this.

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

 

[Fluff]

Another option

If c.Value <> "" Then c.Value = "#" & Trim(Split(c, "#")(1)) & "#"

 

[WaterGypsy]

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)

 

[JEames]

Thank you everyone.

 

[JEames]

Thank you so much, this works perfectly, except your missing a closing bracket at the end of "If"

Try this.

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

 

[WaterGypsy]

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