Macro to Hide Columns Totaling '0' or are Blank

[Tamarissa]

Hi there,

I'm looking for some help to create a macro to hide all columns from A:CE that total '0' or are blank. The totals are currently located in row 135, but this row changes all the time. I'm not sure if that can be taken into account, or if I need to change the macro every time I add or delete rows. At the moment, the totals are just a basic SUM() formula.

Thank you in advance for any input

[mshale]

This one finds the row you have the totals in and runs through that row and hides the 0 values. Does it need to hide the blanks too? In other words, will the cell that would normally have the SUM() be blank?

Please Login or Register  to view this content.

[MarvinP]

Hi mshale,

I remember something about a warning on using "UsedRange" in code and just figured it out.
In your code you assume there is data in Column A when you use this line:
NumOfColumn = ActiveSheet.UsedRange.Columns.Count

If the user has a table that doesn't start in Columnn A then this number will lead your code astray.

I just opened a new workbook and put some text in a few cells (not at the top or left). In the immediate window I typed:

Please Login or Register  to view this content.

and pressed enter which gave me an answer of $C$4:$E$8
When I tried

Please Login or Register  to view this content.

it gave the answer of 3.

This would make your code above not try all the possible sum columns.

Also - I'd unhide all columns before I started your subroutine thinking some values may change and have non zero sums.

I'm thinking I like your code but looking deeper I now understand why UsedRange has some problems.

[Tamarissa]

That is correct, if it doesn't have the SUM() formula, it would be blank, & I would like to have those rows hidden as well.

I will always have data in column A, that is one of the only constants on this particular spreadsheet, although the data that includes the totals starts in row 5, wth the headers in row 4. Generally, I would have everything unhidden to work on the sheet, I am looking for a quick way to hide the '0' & blank column totals only for printing purposes, but thanks for the heads up

I'm running the original macro you wrote, and it seems to go way past column CE (basically it's hiding every column after CE), and then there's an error code 400 that pops up in Microsoft Visual Basic
Below the 'Totals' row, I also have the SUM() formula totaling up smaller ranges of columns, starting in . If that's what's wrong, I can just set up the cell to add each column total individually if that's too difficult to calculate in.

[mshale]

Hi mshale,

I remember something about a warning on using "UsedRange" in code and just figured it out.
In your code you assume there is data in Column A when you use this line:
NumOfColumn = ActiveSheet.UsedRange.Columns.Count

If the user has a table that doesn't start in Columnn A then this number will lead your code astray.

I just opened a new workbook and put some text in a few cells (not at the top or left). In the immediate window I typed:

Please Login or Register  to view this content.

and pressed enter which gave me an answer of $C$4:$E$8
When I tried

Please Login or Register  to view this content.

it gave the answer of 3.

This would make your code above not try all the possible sum columns.

Also - I'd unhide all columns before I started your subroutine thinking some values may change and have non zero sums.

I'm thinking I like your code but looking deeper I now understand why UsedRange has some problems.

You are correct, but I don't know an easy way of counting the columns without usedrange. I know another way for rows, but don't know if it can be applied to the columns:

Please Login or Register  to view this content.

also, I didn't unhide the whole sheet on purpose because it's possible that the user doesn't want anything other than the specified range to be touched However, I figured this was a one time macro anyway lol.

That is correct, if it doesn't have the SUM() formula, it would be blank, & I would like to have those rows hidden as well.

I will always have data in column A, that is one of the only constants on this particular spreadsheet, although the data that includes the totals starts in row 5, wth the headers in row 4. Generally, I would have everything unhidden to work on the sheet, I am looking for a quick way to hide the '0' & blank column totals only for printing purposes, but thanks for the heads up

I'm running the original macro you wrote, and it seems to go way past column CE (basically it's hiding every column after CE), and then there's an error code 400 that pops up in Microsoft Visual Basic
Below the 'Totals' row, I also have the SUM() formula totaling up smaller ranges of columns, starting in . If that's what's wrong, I can just set up the cell to add each column total individually if that's too difficult to calculate in.

It's not your data causing the problem. I got in a tiny hurry and forgot to increment "a". Here is the new code that will unhide if the data has become non-zero since the last run of the macro:

Please Login or Register  to view this content.

Marvin: feel free to improve the code

[MarvinP]

@ mshale,

I like code like this
Dim LastRow as Double
LastRow = Cells(Rows.Count,"A").End(xlUp).Row (note - change the "A" to the column where you are sure the data is)

This works for Columns too like
Dim LastCol as Double
LastCol = Cells(1,Columns.Count).End(xlToLeft).Column (note use a row you know data is in instead of 1 in this formula)

So in your code above after you found the row that the sum formula was in (RowNum in your code) you could have done a:
LastCol = Cells(RowNum,Columns.Count).End(xlToLeft).Column (Use this instead of UsedRange for max column count)

I hope this helps.

[Tamarissa]

@mshale, I tried this new macro, and it seemed to work, but after checking all the columns, I found that it seems to be hiding rows that have a total greater than 0 as well for some reason ...

I've attached the file I'm working with, and to reiterate, anything that doesn't have a total of 0 or is blank currently in row 135 (this row fluctuates though) I would like to hide.

[MarvinP]

Hi Tamarissa,
Try this code to see if it does what you want

Please Login or Register  to view this content.

[Tamarissa]

Thanks Marvin, that worked well for me. Just a quick question though, when I have to add & delete rows and the row number changes, all I need to do in the macro is change everything that says '135' to the new row number, correct?

[MarvinP]

Hi - we can use some code from mshale and do it like this...

Please Login or Register  to view this content.

[MarvinP]

OK and for my final answer is...

Please Login or Register  to view this content.

This looks for the words of "Empolyees to be paid:" row and uses that instead of one with a SUM formula in it. It then checks to see how many columns are in that row.

[Tamarissa]

That last option works absolutely perfect, thank you so much !