Clear a range of all data after a certain date obtained from another worksheet

[pbinvestor]

I have a spreadsheet that has about 15 columns of stock data sorted from most recent going back years.
Some stocks have no history before a certain date.
Since I am comparing each stock to each other one I need to eliminate all data that falls befpre the stock with the least amount of data.

So what I am looking for is if I identify the shortest stock history as only 4 years and my longest might be 20 years I want to truncate my table by clearing all data from all stock columns that occured before that date.

I don't want to delete the rows because I have other processes in columns to the right of the last column.

 

[pbinvestor]

Title should have read "before a certain date"
I have already identified the shortest data column in the sheet. JUst need vba to select all columns before that date and clear them.
Thanks...Stupid little problem I know but got hung up on it.

 

[MrTeeny]

Not much to go on but you could simply loop thru the data and clear the cell contents if the data was less than your 'certain date'. Here's a snippet of code that might be useful, we'll assume your dates are in column A1 down and your stock data in A:D columns. For the example just stick the date in cell E1 but obviously you can substitute that with whatever code you use to find the certain date and add extra code to re-sort the data if needed.

Sub ClearRows()
    Dim i As Long
    Dim LRow As Long
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    LRow = Cells(Rows.Count, "A").End(xlUp).Row
    For i = 1 To LRow
    If Range("A" & i).Value < Range("E1").Value Then Range("A" & i & ":D" & i).ClearContents
    Next i
    
    
    Application.ScreenUpdating = True
    Application.EnableEvents = True




End Sub

 

[pbinvestor]

Thanks for the assist.