check Range size

[pioneer]

I have created a worksheet that is designed to take the values fro
another sheet within the same workbook and clean up all values withi
it.

The problem I have is that I want my cleanup worksheet to detec
changes to the size of the range of data that is to be cleaned and cop
down / delete the rows of formulas neccessary to match.
This is instead of having to manually drag down / delete as appropriat
every time I past new data into the RAW sheet.

The cleaned up range is being used as a link table for use within M
Access.

HELP!!

 

[BrianB]

This assums column A has values and column B formulas :-

'-------------------------------------
Sub test()
lastrow1 = ActiveSheet.Range("A65536").End(xlUp).Row
lastrow2 = ActiveSheet.Range("B65536").End(xlUp).Row
'--------------------------
If lastrow1 > lastrow2 Then
ActiveSheet.Range("B1:B" & lastrow1).Formula = _
ActiveSheet.Range("B1").Formula
Else
ActiveSheet.Range(Cells(lastrow1 + 1, 2), Cells(lastrow2
2)).ClearContents
End If
End Sub
'-------------------------------------