Clear table depending on cell and column headers VBA

[Akira181]

I have a number in cell L1 (copied from a different sheet as I can't get vba to reference a cell on a different sheet) and my data is in a table from F to BE, starting at row 4 with headers in row 3.

Everytime the worksheet activates, I need it to clear F4 to the last row of data to whatever column header matches the number in L1.

I've been trying for ages but I cannot get my head around VBA's. Can someone help?

 

[mumps]

Try:

Sub ClearData()
    Dim LastRow As Long, fnd As Range
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Set fnd = Rows(3).Find(Range("L1").Value, LookIn:=xlValues, lookat:=xlWhole)
    If Not fnd Is Nothing Then
        Range("F4").Resize(LastRow - 3, fnd.Column - 5).ClearContents
    End If
End Sub

 

[Akira181]

It was throwing up an error because I had a locked row at the end of the table with subtotals that needed to stay. Changed "LastRow -3" to "-4" and it worked a treat.

Must have spent 4 hours yesterday trying to write something similar and didn't come close. I'll learn VBA this year even if it's the only thing I achieve this year. Thanks a lot!

 

[mumps]

You are very welcome.