Deleting rows below the end of a list that changes in length

Posted by Scott on January 23, 2001 5:10 AM

I have a spreadsheet that runs automatically every day. The sheet opens, runs a Macro to update my list of items (approx. 50) and saves the sheet. The list is imported from an external source and varies in length. The problem occurs when the list shrinks as the items on the bottom are not deleted or over-written. There is an entry at the bottom of the list called "End of list". I want to be able to script the deletion of any rows after this "End of list". If any one can help I'd
appreciate it. Thanks.

Scott

Posted by Celia on January 23, 2001 5:54 AM

It might be easier to delete the old data before importing the new list.
If your data starts in cell A3 (let's say), you could put the following at the start of your macro:-

Range("A3:A65536").EntireRow.Delete

or if the data never exceeds 200 rows (for example) :-

Range("A3:A203").EntireRow.Delete

Celia

Posted by Dave Hawley on January 23, 2001 6:10 AM

Hi Scott

Couldn't you just clear the whole Column first ? with:

Sub WholeColumn()
Columns(1).Clear
End Sub

Or do only clear from "End of list" down you could use:

Sub EndDown()
Dim CellSrt As Range, Cellend As Range
Set CellSrt = Columns(1).Find("End of list", [A65536])
Set Cellend = CellSrt.End(xlDown)
Range(CellSrt, Cellend).Clear
End Sub

Both assume it's the Active sheet.

Dave
OzGrid Business Applications