How about
Dim myWS as Worksheet
Dim lRow as long
Dim myRange as range

Set myWS = activesheet

lRow = myWS.cells(rows.count,1).end(xlup).row
set myrange = myWS.cells(1,1).resize(lrow,1)
myrange.clearcontents
set myrange = nothing

'Now run your code

HTH,
Barb Reinhardt

"Kai Cunningham" wrote:

I am using VBA code I found on this board to populate a summary sheet with
data from other worksheets in the same workbook. The code is as follows:

Sub SheetList()
For Each ws In mywb.Worksheets
i = i + 1
Cells(i, 1) = ws.Name
Next
End Sub

This is working great for what I need it to do, however, when I delete a
worksheet it creates a broken reference for the last row in the set because
my formulas in the rest of the row depend on references to the first cell in
the row to locate the worksheet they are pulling information from (INDIRECT
references for those in the know). Deleting two worksheets results in the
last two rows worth of references being broken, and so on. How can I edit
this code to both generate the list like I want it to, and then delete the
rest of the entries in column "A" to clear the bad references from that row
of information? I am ok with these rows showing a zero, but would be
blissfully happy if someone could point me to some code that will move the
totals line up and down as I add or subtract worksheets to the workbook!
Thanks for your time!
-Kai