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:

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,831
Office Version
  1. 365
Platform
  1. Windows
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:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,416
Office Version
  1. 365
Platform
  1. Windows
Another option
Code:
If c.Value <> "" Then c.Value = "#" & Trim(Split(c, "#")(1)) & "#"
 

WaterGypsy

Well-known Member
Joined
Jan 15, 2010
Messages
697
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

New Member
Joined
Nov 17, 2014
Messages
12

ADVERTISEMENT

Thank you everyone.
 
Last edited:

JEames

New Member
Joined
Nov 17, 2014
Messages
12
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
 

WaterGypsy

Well-known Member
Joined
Jan 15, 2010
Messages
697
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:
 

Watch MrExcel Video

Forum statistics

Threads
1,109,142
Messages
5,527,069
Members
409,742
Latest member
setam

This Week's Hot Topics

Top