Delete part of cell value

[Sinner]

Hi,

Below code removes word 'SADMIN' if found in each cell of column M of
sheet2.
----------------------------------------------------------------------
Sub RMV_SADMIN()
Application.ScreenUpdating = False
Application.DisplayAlerts = False

For Each Cell In Range("sheet2!M:M")

sStr = Trim(Cell.Value)
If Left(sStr, 6) = "SADMIN" Then
Cell.Value = Trim(Right(sStr, Len(sStr) - 6))

End If

Next

Application.ScreenUpdating = True
Application.DisplayAlerts = True

Worksheets("Sheet2").Activate
Range("A1").Select

End Sub

 

[Dave Peterson]

Is it ok to get rid of all the SADMIN's?

Sometimes, it takes longer to loop through all the cells instead of just doing a
single Edit|Replace.

Record a macro when you do:
Select the column
Edit|Replace
What: SADMIN
with: (leave blank)
replace all

(The asterisk is a wild card that represents all the trailing characters.)

Then tweak the code to get rid of the selects:

with worksheets("Sheet2").range("m1").entirecolumn
.cells.replace What:="SADMIN", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
End with

I'm not sure if matching case is important to you, though.

And you're also adding that trim() call. So your code is doing more than what
mine would do.

 

[Rick Rothstein \(MVP - VB\)]

The first improvement you can do to your code is to indent it (see me
modification of your code later on in this message). The second improvement
would be to declare all your variables. Along with this, all of your
procedures should include an Option Explicit statement so you can be warned
if you misspell a variable name somewhere within your code (not a problem
with your posted code, but this is still a good thing to do). You can
include the Option Explicit statement automatically by selecting
Tools/Options from the menu bar in the VB editor and putting a checkmark
next to "Require Variable Declaration" on the Editor tab. After doing that,
all new code windows (not existing ones) will have the Option Explicit
statement automatically added to them.

Okay, now to the code you posted. I would eliminate this statement...

sStr = Trim(Cell.Value)

since this statement performs the identical functionality for you...

Cell.Value = Trim(Right(sStr, Len(sStr) - 6))

Also, with the above statement, I would replace the Right function call with
a Mid statement call instead... if you leave off the optional 3rd argument,
the Mid function will start at the specified character and return the
remainder of the text (this eliminates having to subtract 6 from the length
of the original text). I notice you use Range("Sheet2!M:M") to reference
Column M on Sheet2; while there is nothing technically wrong with this, my
personal preference is to separate references using the Worksheets function
and, since it is a Column reference, the Columns function.

Here is how your function would look with my suggested changes and
preferences...

Sub RMV_SADMIN()
Application.ScreenUpdating = False
Application.DisplayAlerts = False

For Each Cell In Worksheets("Sheet2").Columns("M")
If Left(sStr, 6) = "SADMIN" Then
Cell.Value = Trim(Mid(sStr, 6))
End If
Next

Application.ScreenUpdating = True
Application.DisplayAlerts = True

Worksheets("Sheet2").Activate
Range("A1").Select
End Sub


Rick