VBA to hide all blank worksheets

[Cknight]

I have been searching online but cannot seem to find an answer to this.

I have an order sheet that has all of my vendors listed on separate worksheets. We do not order from every vendor during every order period so some sheets will end up being blank.

I would like a macro to run that hides any supplier worksheet that has no data on it.

Alternatively, I could also run the macro if a certain cell, like B1 is blank to hide the sheets as well. Either would be perfect for what I am looking to accomplish.

Thanks in advance for any help!!

 

[Nebu]

Hi:

This is pretty easy to achieve , would you care uploading a sample workbook please.

Thanks

 

[Cknight]

Nebu-

Thanks for your help!
I attached the file but had to cut a lot of the data out to get under the 1MB limit.

I have 83 vendors total but the same process would need to apply to those worksheets and any new vendors we may add in the future.

So in the file provided, I would be looking for a macro that would hide the worksheets:
COCAMO-Case Qty
COLLINS-Case Qty

The other worksheets would stay unhidden since it has data in it below the first row.

The cell, B2 will always have data in it for sheets that will remain unhidden and it will be blank for sheets that need to be hidden.

Thanks again

 

[Nebu]

Hi:

Use the following code.

Sub HideSheet()
Application.ScreenUpdating = False

Dim Ws As Worksheet

For Each Ws In Worksheets
If Application.WorksheetFunction.CountA(Ws.Range("B:B")) < 2 And Ws.Name <> "Sheet1" Then
    Ws.Visible = False
End If
Next
Application.ScreenUpdating = True
End Sub

The code will hide those sheets which will have no product information.
Go to the "Sheet1" and click on the Hide button for the Macro to run.
Thanks

 

[Cknight]

That works perfectly. Thanks so much!!