Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete part of cell value
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 ----------------------------------------------------------------- Any further improvement is welcomed. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete part of cell value
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. Sinner wrote: 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 ----------------------------------------------------------------- Any further improvement is welcomed. -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete part of cell value
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 "Sinner" wrote in message ... 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 ----------------------------------------------------------------- Any further improvement is welcomed. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete part of a cell | Excel Worksheet Functions | |||
Delete Part of Cell? | Excel Programming | |||
Delete Part of Cell? | Excel Programming | |||
Delete Part of Cell? | Excel Programming | |||
Delete Part of Cell? | Excel Programming |