Selection names not clearing for next race in Excel

[billstickers]

Sometimes I am finding that some selection names in column A of my spreadsheet are remaining from the previous race, if the current race has less runners than the previous race. As I am using this field to make decisions, this then causes an error in Excel and stops my VBA program from running.

[GaryRussell]

I'm not aware of this issue.

How often does it occur?
Which racing markets are affected? UK, US etc.
Can you reproduce this problem by logging to a blank Excel sheet and switching markets until the problem occurs or does it only affect your spreadsheet?

[billstickers]

This issue has been occuring about once a day for a week or so. It seemed to start when I was looking at US racing markets, but is happening now in UK markets. It does not happen every time, just sometimes.

I will try to reproduce the problem as you have suggested. If I can reproduce it, or when it happens again, shall I send you a copy of the spreadsheet? If so what email address shall I send it to?

[MarkRussell]

Hi,

Please send to admin@gruss-software.co.uk

Thanks,
Mark

[jokerjoe]

Not sure if this is relevant, it might affect your code, but I noticed that it takes a couple of refreshes (of the 16 main columns) to clear the previous runners in the situation you describe (less runners in new race). As a workaround at a change of market I wait for a few refreshes to pass before allowing any code to run.

[billstickers]

Thanks Jokerjoe, I think that may be the solution. What is the best way to allow a few refreshes to happen, before running any code?

[jokerjoe]

I put a refresh counter in as a module-wide variable, the basis for all my worksheets will look something like this:

Code: Select all

Private MyMarket As String
Private RefreshCount As Long

Private Sub Worksheet_Change(ByVal Target As Range)
   
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
   
    Select Case Target.Columns.Count
        Case 16 'odds and market info update
           
            'check to see if market changed
            If wsBA.Range("A1").Value = MyMarket Then
                RefreshCount = RefreshCount + 1
            Else
                MyMarket = wsBA.Range("A1").Value
                RefreshCount = 1
            End If
                       
            If RefreshCount > 3 Then
           
                    '#################  CODE HERE
           
            End If
                       
        Case 5 'bets update
        Case 1 'refresh update
        Case 8 'bet column headers update
        Case Else
    End Select

    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic

End Sub



Possibly being a bit over the top with the case specifying! I believe theres a 2-column update too, just after the bets get read.

[billstickers]

My code is in a Private Sub Worksheet_Calculate(), do I scap that and put it all in where you say ####CODE HERE, in a Private Sub Worksheet_Change(ByVal Target As Range)?

[jokerjoe]

Yup pretty much.

[billstickers]

Thanks Jokerjoe, I think I've cracked this now.

Cheers