Removing 'blank' workbooks

[antihippy]

Hello!

I've a workbook. There are a number of worksheets. Each worksheet relates to a specific country (austria, Belgium, Croatia etc). I use VBA to plonk data on each sheet, however (and this bit varies from month to month) there is never data on all worksheet - this is expected. What I need help on is removing the excess worksheet. The simple way would appear to test each worksheet for anything below a certain row and if the answer is "No there is no data" drop the sheet. I can't get my head round this. It seems simple, can anyone help?

 

[xlbo]

sub Remove_Empty_Sheets()
For each sht in thisworkbook.worksheets
    if sht.cells(65536,1).end(xlup).row = 1 then sht.delete
next
end sub

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[antihippy]

Hi Geoff,

I've tried your code but I can't get it to work.

 

[xlbo]

In what way can you not get it to work ??


Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[antihippy]

That reply was a bit rushed and I seem to have cut off the rest of it ...

Well I got one of this months spreadsheets and pasted your code in. Added in a couple of extra worksheets with dummy data and ran the code. Nothing happened - not even on the 'blank' sheets.

The dummy data was just in the first couple of cells. My description may have been incorrect - there will be entries data in the first 4 rows (this has been pulled from the other sheets as part of the first stage of report).

 

[PHV]

You may try something like this:
Sub Remove_Empty_Sheets()
For i = ThisWorkbook.Worksheets.Count To 1 Step -1
If ThisWorkbook.Worksheets(i).Cells(65536, 1).End(xlUp).Row <= 4 Then
ThisWorkbook.Worksheets(i).Delete
End If
Next
End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[xlbo]

Ok - the code checks to see what the last populated row in column A is

If the answer is 1 then it deletes the sheet as that indicates no data

If you do not have data in column A, then change the 1 in cells(65536,1) to reflect a column that SHOULD have data in it (ie A=1, B=2 etc etc)

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[antihippy]

Hmmmmm ....

I've tried both of your suggestions yet I cannot get either to work.

XLBO: I've altered your VBA several time (trying alternate values) and it doesn't appear to work.

PHV: Same problem ..

I can send off a sample if anyone would like to see it?

It's hot in the office today and it's entirely possible that the reason I am struggling with this is because I am over heating...

 

[antihippy]

To complete the picture:

There is an entry left in the cells a1, a2 and a -> n 4.

So for XLBO's code I've changed the value to 4 (and even tried 3 and 5 to see what the difference might be.

I've tried the same with PHV's - and also changed the operator [<,>,<=,>=,<>] with out any success.

 

[xlbo]

So there will be text to row 4 in ALL sheets ??

and you want to delete those which have text NO FURTHER than row 4 ??

in which case:

sub Remove_Empty_Sheets()
For each sht in thisworkbook.worksheets
    if sht.cells(65536,1).end(xlup).row = 4 then sht.delete
next
end sub

Only reason this won't work is if there are formulae that are set to blank if there is no data - excel sees these as having data in them even if that data happens to be a zero length string...

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question