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.
Any thoughts on what I have done incorrectly, or what amendment needs to be made to have it work properly?
Thanks in advance
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