Hide column if all cells down the column are zero by using macro in excel

[Azrin]

Hello, everyone.

I really need help in hiding specific column in excel where all the cells down that column are zero.

For example, column A that has 50 rows.
If all the cells in column A contain 0, then the column will be automatically hidden.

Can I accomplish this by using macro?

I have used and modified several macros from other users' solutions but they were not working the way I want it.

Will anyone help me solve this problem?

 

[mirabeau]

here's a couple of possible macros.

to enable you to see if the right columns are chosen they are colored yellow.

if that looks good to you then change the c.InteriorColor=VBYellow to c.Hidden = True

Dim c, d, flg As Byte
For Each c In ActiveSheet.UsedRange.Columns
    flg = 0
    For Each d In c.Value
        If d <> 0 Then
            flg = 1
            Exit For
        End If
    Next d
    If flg = 0 Then c.Interior.Color = vbYellow
Next c
End Sub

Sub two()
Dim c
For Each c In ActiveSheet.UsedRange.Columns
   If Application.CountIf(c, 0) = Application.CountA(c) Then _
        c.Interior.Color = vbYellow
Next c
End Sub

 

[Azrin]

Thanks for the solution and help, mirabeau

Hi, mirabeau.
Thanks for the solutions as well as the help.

I have applied both of the solutions, they are working fine but the affected columns are not the ones that I targeted at.

Sorry for not detailing my problem in a more specific way. My spreadsheet is consisted of Column A till Column BZ.

Column A till Column C are reserved for information such as the pets' ID, locations of the shelter house, and age.

Hence, the columns that I want to hide will be starting from columns D until column BZ, where each of the cells in this column detailing information about the volunteers that helped in taking care of these animals.

One volunteer can help taking care any of the pets as long as the pets are not taken care of, and thus the volunteer is marked either 1 or 0. 1 means that the volunteer is currently taking care of a pets vice versa.

Plus, we also used filter to view the data. The filter is applied on column A where the pets' IDs are stored. For example, 1002, the 1 in front means it is a dog and 2038, the 2 means it is a cat. So, that we can monitor the number of cats and dogs that we have in our shelter.

Moreover, the number of cats and dogs will be either increasing or decreasing (continuously updating) with time.

Below is how the spreadsheet almost looks like:


A B C D E F G H I J K L M N O P Q R S T U V W X Y Z
1 1 0
2 1
3 0 1
4 0
5 1
6 1
7 0

I hope this information will help you or other members out there to help me solve this problem. As a non-profit organization, we don't have the budget to hire a developer to this for us.

Thank you.

 

[mirabeau]

Re: Thanks for the solution and help, mirabeau

Hi, mirabeau.
Thanks for the solutions as well as the help.

I have applied both of the solutions, they are working fine but the affected columns are not the ones that I targeted at.

So what are the ones that you targeted at if not containing all zeros?

Your exposition in Post #3 didn't enlighten me at all on that point.

I understood that hiding columns with all zeros in the cells was the purpose of your thread. What else is it that you want??

 

[Azrin]

Thanks for the macro, Mirabeau, they work but I still need help for the range

Hello Mirabeau and other members,

Thank you for the reply as well as the help.

I'm so sorry if I have confused you with irrelevant information.

I've applied both of the solutions that you provided, they're working fine and executable.

The only issue is regarding the range of the columns.
Both of the macros highligted column C to CM and I have tried to modify the macro to affect column D to BZ, but until now, I'm still unable to do that.

Do you mind modifying the macro so that it'll affect column D until column BZ? I have tried changing the range but all my efforts were to no avail.


Thank you.

 

[mirabeau]

Re: Thanks for the macro, Mirabeau, they work but I still need help for the range

Do you mind modifying the macro so that it'll affect column D until column BZ? I have tried changing the range but all my efforts were to no avail.


Thank you.

modifying the second code as below. does this do what you want?

Sub twomod()
Dim c
For Each c In Intersect(ActiveSheet.UsedRange, Range("D:BZ")).Columns
   If Application.CountIf(c, 0) = Application.CountA(c) Then _
        c.Interior.Color = vbYellow
Next c
End Sub

 

[Azrin]

Thanks, Mirabeau

Thank you so much, MIrabeau.

After just minor tweaks, the code that you provided worked wonder.

You're simply the best!!!