Loop that deletes sheets if data ends on a certain row

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!

For count = 15 to 1 step -1

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

next count

--
Regards,
Tom Ogilvy

"Please Help" <[email protected]> wrote in message
news:[email protected]...
> 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!
>
>
>
>

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


>end if
>
>next count
>
>Please let me know your suggestions for solving this
problem. Thanks in
>advance for your help!
>
>
>
>
>.
>

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


"Please Help" wrote:

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

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

"Please Help" wrote:

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