Macro adding unwanted characters

honkin

Active Member
Joined
Mar 20, 2012
Messages
385
Office Version
  1. 2016
Platform
  1. MacOS
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.

VBA Code:
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
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
The X is the result of the formula that you are applying to the 1st blank column for filtering.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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

VBA Code:
.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
 
Upvote 0

Forum statistics

Threads
1,223,910
Messages
6,175,320
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top