Macro adding unwanted characters

[honkin]

I have a macro I use to look for ONLY selections from Ireland. The track names are all listed and everything in the macro works fine, except a huge number of unwanted characters are placed in the first available column after the data. The character is always this - X

As I said, the macro filters everything correctly, but once certain columns are hidden, the letter X is placed in the first available column before everything is copied to another workbook.

Sub FA_Racing_1()
'
' FA_Racing_1 Macro
' VDW Rank, RnkPFP, Distance, PR Odds, Handicap
'
    Dim ws As Worksheet, lc As Long, lr As Long


    Set ws = ActiveSheet
    'range from A1 to last column header and last row
    lc = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
    lr = ws.Cells.Find("*", after:=ws.Range("A1"), LookAt:=xlPart, _
                        SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    With ws.Range("A1", ws.Cells(lr, lc))
        With .Cells(2, .Columns.Count).Resize(.Rows.Count - 1)
            .FormulaR1C1 = "=if(or(rc8={""Ballinrobe"",""Bellewstown"",""Clonmel"",""Cork""," & _
            """Curragh"",""Downpatrick"",""Down Royal"",""Dundalk"",""Fairyhouse""," & _
            """Galway"",""Gowran Park"",""Kilbeggan"",""Killarney"",""Laytown"",""Leopardstown""," & _
            """Limerick"",""Listowel"",""Naas"",""Navan"",""Punchestown"",""Roscommon""," & _
            """Sligo"",""Thurles"",""Tipperary"",""Tramore"",""Wexford""}),""X"","""")"
            .Value = .Value
         End With
        .HorizontalAlignment = xlCenter
        .AutoFilter .Columns.Count, "X"
        .AutoFilter Field:=3, Criteria1:="<>*Handicap*"
        .AutoFilter Field:=39, Criteria1:="<=5"
        .AutoFilter Field:=24, Criteria1:="=~*", _
        Operator:=xlOr, Criteria2:="=~*~*"
        .AutoFilter Field:=71, Criteria1:="1"
        .AutoFilter Field:=78, Criteria1:="<=7"
        .AutoFilter Field:=57, Criteria1:="<=3"
         If .Rows.Count - 1 > 0 Then
        On Error Resume Next
        .Columns("C:C").EntireColumn.Hidden = True
            .Columns("G:G").EntireColumn.Hidden = True
            .Columns("I:I").EntireColumn.Hidden = True
            .Columns("K:L").EntireColumn.Hidden = True
            .Columns("N:W").EntireColumn.Hidden = True
            .Columns("Z:AK").EntireColumn.Hidden = True
            .Columns("AO:AO").EntireColumn.Hidden = True
            .Columns("AQ:BD").EntireColumn.Hidden = True
            .Columns("BF:BJ").EntireColumn.Hidden = True
            .Columns("BM:BR").EntireColumn.Hidden = True
            .Columns("BT:BY").EntireColumn.Hidden = True
            .Columns("CA:CC").EntireColumn.Hidden = True
        If .Columns(1).SpecialCells(xlVisible).Count > 1 Then
        .Offset(1, 0).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Copy
        Else
            Exit Sub
        End If
            On Error GoTo 0
        End If
    End With
    
    Workbooks("New Results File Active.xlsm").Sheets("FA Racing 1") _
          .Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
  
    Application.CutCopyMode = False
End Sub

Any thoughts on what I have done incorrectly, or what amendment needs to be made to have it work properly?

Thanks in advance

 

[Fluff]

The X is the result of the formula that you are applying to the 1st blank column for filtering.

 

[honkin]

The X is the result of the formula that you are applying to the 1st blank column for filtering.

Thanks Fluff, though I already knew it was due to the formula I had written; that was the point of my post. Something is amiss in that formula and it is beyond my skills to spot it and fix it.

cheers

 

[Fluff]

Then why did you say

except a huge number of unwanted characters are placed in the first available column after the data. The character is always this - X

The formula is simply putting an X in the column if col H equals any of those places.

 

[honkin]

Then why did you say

The formula is simply putting an X in the column if col H equals any of those places.

Fluff, I know unwanted characters are being added and I know it is in the code I grabbed from somewhere. You asked, "why did you say...?" Does it not seem patently obvious that the reason I wrote, "except a huge number of unwanted characters are placed in the first available column after the data. The character is always this - X" is because I actually did't know how to resolve the issue? It was never my code to begin with.

The macro should ONLY be autofiltering and then copying, nothing more, but it is incorrect. I know that; I just don't know how to fix it. The code with all the Irish track names is what should be being autofiltered, along with a handful of other columns. The result is then copied to another sheet. It was a suggestion from a forum that I now cannot recall and it was an attempt to do this without using an array for whatever reason. That whole code with Irish track names is simply to autofilter for ONLY those Irish tracks listed.

In the end, I a fair bit of time and played around with doing it as a simple multi-line array, but just had to work out what had to come out, which was the extra "" characters, as well as the ones before and after the &, as well as the last comma in each line

The new array is this

.AutoFilter Field:=8, Criteria1:=Array("Ballinrobe", "Bellewstown", "Clonmel", "Cork" & _
            "Curragh", "Downpatrick", "Down Royal", "Dundalk", "Fairyhouse" & _
            "Galway", "Gowran Park", "Kilbeggan", "Killarney", "Laytown", "Leopardstown" & _
            "Limerick", "Listowel", "Naas", "Navan", "Punchestown", "Roscommon" & _
            "Sligo", "Thurles", "Tipperary", "Tramore", "Wexford"), Operator:=xlFilterValues

It seems to work, so will leave it as is now

cheers