Loop that deletes sheets if data ends on a certain row

[Guest]

Hi everyone,

I need to have a loop that goes through all of the sheets I've created as
part of my macro output and deletes the sheets that contain no information.
These sheets will all have data that will end on row #7. I'm having problems
with this because when you delete a sheet it seems to change the reference
point of the active sheet and the next thing you know i'm getting a
"subscript out of range" error.

I've tried to do this by using a FOR loop that loops thru each sheet and if
the last row of data is a 7, deletes the sheet...no success. The total
number of sheets will always be 15. It looks something like this:

For count = 1 to 15

lastrow = (lastrow finder code here)
If lastrow = 7 then
activesheet.delete
end if

next count

Please let me know your suggestions for solving this problem. Thanks in
advance for your help!

 

[Markus Scheible]

Hi unknown,

could you please use your real name?

For count = 1 to 15

lastrow = (lastrow finder code here)
If lastrow = 7 then
activesheet.delete

use sheets(count).delete instead


Best

Markus

 

[Tom Ogilvy]

For count = 15 to 1 step -1

lastrow = (lastrow finder code here)
If lastrow = 7 then
activesheet.delete
end if

next count

 

[Guest]

The best way to loop and delete is with a For Each... Next loop; e.g.

Dim ThisSheet as Worksheet

For Each ThisSheet in ThisWorkbook.Worksheets
If (lastrow finder code) then ThisSheet.delete
Next ThisSheet

 

[Guest]

Thanks To All for your help.. that was fast and it works great!!! I used
Tom's suggestion since it was the easiest change for my code.

Thanks!
Curtis