Delete Sheets according to cell value

[aequitas1903]

Hi everyone,

I am working on a project and I was a little confused how to do it with vba and lack of knowledge. Hope you guys can help me.

I will try to explain the situation, I got an excel which has nearly 800 sheets and in sheet named "Summary" where B column has numbers from 100 to 799 (All this numbers have specific sheet for themselves. Sheet names are like 100,101,102 ,....,799) and column E has a summary of absolutes of column C + column D. I wish to delete the sheets which F column value is zero (meaningless numbers). I can show you a little example what I mean.

	B	C	D	E
1	100	12	13	=abs(c1)+abs(c2) =25
2	101
3	102	9	-9	18
4	103	3		3

<TBODY>
</TBODY>

As mentioned before I got 800 values in E column. I can filter and see which numbers are zero. In the table above sheet named 101 should be deleted.

I thought to use a formula (=vlookup(101,'summary'!A:E,5.FALSE) in A1 cell in every sheet to see if the value of the number is zero and delete that sheet. For example the macro will check A1 column in everysheet and if the value is zero it will delete the sheet entirely. Or It can filter zero valued numbers in column E in "Summary" sheet and delete sheets from there.

Is it possible to do something like this ? I appreciate your thoughts and knowledge and it will save my hours if it is possible. I have to change number values periodicly and have to analyze them all everytime.

Any help is appreciated. Any ideas can give me a new perspective.

Thanks for your help.
Kind Regards

 

[Smitty]

If you step through the code, does this actually contain any values:

Sheets(Trim(ws.Cells(c.Row, "C").Value)).Delete

 

[aequitas1903]

Yeap. The outcome is ok. The code searches all values and filters the unwanted sheets and deletes them. But after all of them is finished it gives the error. I guess there is a problem with the range definition. Couldnt find a way to figure it out.

 

[Smitty]

If it's completing fine, and you can't figure out where it breaks, then you can add On Error Resume Next to skip past the error. Just note that it doesn't address the error, just moves past it.