CLEAR COLUMN

[BERTRAND]

Code: Select all

Option Explicit

Dim CellA1 As String

Private Sub Worksheet_Calculate()
 Dim varArray() As Variant
 
 With Sheet6
    If CellA1 <> .Cells(1, 1).Value Then
        CellA1 = .Cells(1, 1).Value
        varArray = .Range("A1:A50").Value
        .Range("B10000").End(xlUp).Offset(1, 0).Resize(UBound(varArray, 1), UBound(varArray, 2)).Value = varArray
    End If
End With

End Sub


I am using the above code to Log prices in column B. I would like to clear this column when the next race loads. Can someone tell me how to achieve this?
Many thanks Bert

[osknows]

Try this in the sheet object where the BA prices update.

Code: Select all

Option Explicit

Dim currentMarket As String

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Columns.Count <> 16 Then Exit Sub
   
    Application.EnableEvents = False

    With Target.Parent
       
        If .Range("A1").Value <> currentMarket Then
       
               currentMarket = .Range("A1").Value
               Sheet6.Range("B:B").ClearContents
        End If
       
    End With
   
    Application.EnableEvents = True

End Sub

[BERTRAND]

Thanks Os
Please excuse my woeful lack of VBA knowledge.
I have this inplay timer in sheet1. How do I integrate your code?

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
If updating Then Exit Sub
Application.EnableEvents = False
updating = True
With Sheet1
    If .Cells(2, 5) = "In Play" Then
    If .Cells(1, 27) = "" Then .Cells(1, 27) = .Cells(2, 2)
    If .Cells(1, 27) <> "" Then .Cells(1, 28) = .Cells(2, 2) - .Cells(1, 27)
    End If
    If .Cells(2, 5) <> "In Play" And .Cells(2, 6) <> "Suspended" Then
    .Cells(1, 27) = ""
    .Cells(1, 28) = ""
    End If
End With
updating = False
Application.EnableEvents = True
End Sub

[osknows]

Code: Select all

Option Explicit

Dim currentMarket As String
Dim updating As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)

If updating Then Exit Sub
Application.EnableEvents = False
updating = True

With Sheet1

    If .Range("A1").Value <> currentMarket Then
       
        currentMarket = .Range("A1").Value
        Sheet6.Range("B:B").ClearContents
    End If
   
    If .Cells(2, 5) = "In Play" Then
   
        If .Cells(1, 27) = "" Then .Cells(1, 27) = .Cells(2, 2)
        If .Cells(1, 27) <> "" Then .Cells(1, 28) = .Cells(2, 2) - .Cells(1, 27)
       
    ElseIf .Cells(2, 5) <> "In Play" And .Cells(2, 6) <> "Suspended" Then
   
        .Cells(1, 27) = ""
        .Cells(1, 28) = ""
    End If
   
End With

updating = False
Application.EnableEvents = True
End Sub