Please help removing my colons

[WalkAlot]

Hi all,

I've got a large Excel document with about 16 worksheets. Alot of the cells have a colon at the end of the strng, but not all. I need to remove all of the colons sp that my Index Match formulas will work. I've recorded quide a detailed Macro and added the code in to remove the colons as I found a similar post about removing semicolons earlier but this just isn't working for me. I'm using Ecel 2016 but working of a 97-2003 workbook in compatability mode. My full code is below:

Sub Run_Monthly_Reporting()
'
' Run_Monthly_Reporting Macro
' jdioq dquihdui dygdoydw dygdw uiyegwd
'
' Keyboard Shortcut: Ctrl+m
'
    ActiveWorkbook.RefreshAll
    Columns("D:D").Select
    Selection.Copy
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("F:F").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("I:J").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveWindow.SmallScroll ToRight:=6
    Columns("M:O").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Total e-Comm Eng").Select
    Columns("A:D").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("LY e-Comm").Select
    Columns("A:C").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Desktop Data").Select
    Columns("A:F").Select
    Application.CutCopyMode = False
    Selection.Copy
    Columns("A:G").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Desktop Eng").Select
    Columns("A:D").Select
    Application.CutCopyMode = False
    Selection.Copy
    Columns("A:D").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Tablet Data").Select
    Columns("A:G").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Tablet Eng").Select
    Columns("A:D").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("e-Comm on Phone Data").Select
    Columns("A:G").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("e-Comm on Phone Eng").Select
    Columns("A:D").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Mobile").Select
    Columns("D:J").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Mobile Eng").Select
    Columns("A:D").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("LY Mobile").Select
    Columns("A:C").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveWindow.ScrollWorkbookTabs Sheets:=-1
    ActiveWindow.ScrollWorkbookTabs Sheets:=-1
    ActiveWindow.ScrollWorkbookTabs Sheets:=-1
    ActiveWindow.ScrollWorkbookTabs Sheets:=-1
    ActiveWindow.ScrollWorkbookTabs Sheets:=-1
    Sheets("Worksheet").Select
    Range("A1").Select
    Application.CutCopyMode = False
    Cells.Replace What:=Chr(58), Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="m:ar:brandedshop:", Replacement:="", LookAt:=xlPart _
        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="ar:brandedshop:", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="m:ar:shop:", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="ar:shop:", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="m:ar:static:", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="ar:static:", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="m:ar:buyersguide:", Replacement:="", LookAt:=xlPart _
        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="ar:buyersguide:", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="ar:inspireme:", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="ar:events:", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Range("K3:K1499").Select
    ActiveWindow.SmallScroll ToRight:=-4
    Range("F3:F1499").Select
    Selection.NumberFormat = "0"
    Range("I3:I1499").Select
    Range("I1499").Activate
    Selection.NumberFormat = "0.00%"
    Range("J3:J1499").Select
    Selection.NumberFormat = "$#,##0.00"
    ActiveWindow.SmallScroll ToRight:=5
    Range("M3:M1499").Select
    Range("M1499").Activate
    Selection.NumberFormat = "0"
    Range("N3:O1499").Select
    Selection.NumberFormat = "$#,##0.00"
    Range("E3").Select
    ChDir "C:\Users\williaki\Desktop"
End Sub

And an example of the colons is:

[TABLE="width: 415"]
<tbody>[TR]
[TD="class: xl106, width: 415"]halfterm:[/TD]
[/TR]
[TR]
[TD="class: xl106"]wow:[/TD]
[/TR]
[TR]
[TD="class: xl106"]valentines:[/TD]
[/TR]
</tbody>[/TABLE]

 

[Drrellik]

you could modify the code to remove all colons if you want like this

Sub Replace_Colon()
'
' replace all ":" with ""
'
'
    Cells.Replace What:=":", Replacement:="", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End Sub

 

[Drrellik]

or if you want to do it manually with Find+Replace the shortcut keys are Crtl+H that way you can control the next or all function.

 

[WalkAlot]

Thanks for taking the time to have a look at this for me Drrellik. The 2 methods you've mentioned above don't seem to work either I think because I'm trying to remove a punctuation mark, these were 2 2 ways I tried before moving to the Chr ref.

 

[rlv01]

Here's a way to remove just the trailing colons in cells that are used.

Sub RemoveTrailingColons()
    Dim R As Range
    Dim S As String

    For Each R In ActiveSheet.UsedRange
        S = RTrim(CStr(R.Value))
        If Right(S, 1) = ":" Then
            Do While Right(S, 1) = ":"
                S = Left(S, Len(S) - 1)
            Loop
            R.Value = S
        End If
    Next R
End Sub

 

[Logit]

.
You weren't completely clear about the colons. If your colons are ONLY at the end of the string, and never located within the string, this should work:

Option Explicit


Sub FindReplaceAll_CountReplacements()


Dim sht As Worksheet
Dim ReplaceCount As Long


Dim fnd, rplc As String


fnd = ":"
rplc = ""


For Each sht In ActiveWorkbook.Worksheets


  ReplaceCount = ReplaceCount + Application.WorksheetFunction.CountIf(sht.Cells, "*" & fnd & "*")


  sht.Cells.Replace what:=fnd, Replacement:=rplc, _
    LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
    SearchFormat:=False, ReplaceFormat:=False
    
Next sht


MsgBox "I have completed my search and made replacements in " & ReplaceCount & " cell(s)."


End Sub

Keep in mind, this macro removes ALL COLONS regardless of the location.

 

[Fluff]

.
If your colons are ONLY at the end of the string, and never located within the string, this should work:

Keep in mind, this macro removes ALL COLONS regardless of the location.

 

[Logit]

Keep in mind, this macro removes ALL COLONS regardless of the location.

 

[WalkAlot]

Thanks Riv01 and Logit, this is awesome. I can complete what I need to do now. Thanks you

Only problem (not much of an issue) I use Logit's code to remove all colons which is does apart from on the 1st sheet, I then run Rivo1's code on the first sheet and that removes eveything from there.

Riv01's Macro just does 1 sheet at a time which is great but takes a while to get through everything.

Is there a way of getting Logit's macro to include sheet 1 'Master' or combining the 2 macros so that Riv01's macro automatically moves on to the next sheet and hits the end?

Thanks

 

[rlv01]

Like a lot of things in programming, there is a tradeoff involved. If you don't care if you replace every single colon (no matter its position in the cell data) in every single cell, the method used by Logit is the way to go. It's much faster. If you need it to be restricted to replacing only colons that are the last character of the cell data, while leaving the other colons alone, my example works for that - but it is slower*

Your posted macro looks like something from the macro recorder and there are a lot of things you could potentially do to clean it up and make it run faster, but you should probably start a new thread for that since the thread title is about removing colons.

*(you can speed it up somewhat by limiting it to only the columns were colon removal is needed instead of just telling it to work on every cell in every worksheet)