Clear rows with no data?

[Kai Cunningham]

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 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

 

[Barb Reinhardt]

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]

VBA is hitting a compile error when it gets to "Set" of "Set myWS =
activesheet" saying it has an invalid outside procedure error when I try to
compile it. Any ideas?

 

[Dave Peterson]

Barb just posted the "guts" of the routine.

Option Explicit
Sub YourMacNameHere()

Dim myWS as Worksheet
Dim lRow as long
Dim myRange as range
dim ws as worksheet
dim i as long

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

'Then either call your code:
'Call SheetList
'or add it directly here:

For Each ws In Worksheets
i = i + 1
myws.Cells(i, 1) = ws.Name
Next ws

End Sub

 

[Kai Cunningham]

Thanks a ton! Newer user here and I'm still not familiar with the
programming aspect, so the step-by-step total process is much appreciated!
-Kai