Macro to hide Zero Rows

[Mile029]

Hello, I need a macro to hide zero rows. Basically as you can see in the attachment, if there is a zero in all the columns E-L then the macro should hide that row. If there is a number in any of the columns then the row should not be hidden. Any guidance would be appreciated as I have over 10K rows of data to go through. Thank you.

[Bob Flanagan]

You could try a non-macro solution: In an unused column, sum each row's entries in columns e-l. Then do data filtering, and custom filter this column for all values not equal to zero. If you want, you can do an IF statement =if(sum(e1:l1)=0,0,1). That way, you can just select in the data filter drop down the 1 values.

Robert Flanagan
Add-ins.com LLC
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

[Mile029]

Actually that's what I did orginally, but sometimes the sum of the row equaled zero when you added the data across. I then changed it to if/then for the entire row, column by column, if column E=0 and column F=0.... But I would rather just run a macro and hide the row and unhide the row. Feels cleaner. Any ideas?

[simeon.hruby]

Mile029,

I have a macro that will do that but the problem is that in most of your zero cells the value is not actually zero.
For example Cell E2 displays 0.00 but the true value is 2.21189111471176E-09.
In order for my macro to work the values must truly be 0. Unless that is by design in your spreadsheet and you wish to leave those displayed.

Simeon

[Bob Flanagan]

Simeon, you can make the If statement check each cell:

=if(and(e1=0, f1=0, ......), 0,1)

If you still really want a macro, post back, and I'll kick a small one out.

Bob

[Mile029]

Actually Simon, I would like to round the cells to the zero place. When I had the filter in place, this was my formula:
=IF(AND(ROUND(L2,0)=0,ROUND(K2,0)=0,ROUND(J2,0)=0,ROUND(H2,0)=0,ROUND(F2,0)=0,ROUND(E2,0)=0),"Y","N")

So I basically filtered for the "N", and got the rows I wanted to be visable. But I don't like that long formula there and don't want to use filters so was hoping a macro could do the same by just hiding based on the filter criteria above.

[simeon.hruby]

Bob,

The macro I have now checks each cell for 0 but the problem is in Mile029's workbook. Most of the Cells are displayed as 0 but they really hold a value such as 2.21189111471176E-09 so my macro skips those. I was just wondering if Mile029 wanted those cells to be hidden as well or left alone since they do contain a number.


Simeon

[Mile029]

So to answer your question Simeon, yes I would like those hidden, since they are technically zero to the nth power.

[simeon.hruby]

Mile029,

Try this spreadsheet and see if this is what you were looking for.

In the macro I have it looking for a value that is less than 1 in those cells and if it is it will change it to 0 and then go through and hide all of the unwanted 0 rows. Ctrl + h will run the macro. Hope this helps!


Simeon

[Bob Flanagan]

Try this code:

Please Login or Register  to view this content.

Robert Flanagan
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

[Mile029]

Hi Simeon, 2 problems:
1. the macro repalces anything less than 1 with a zero, I do not want to change any of the values of the cells. Since some of the accounts will have a negative balance I do not want to replace anything with zero. It is basically if zero value to the zero power, then hide, not if less than one, make zero, then hide. There are formulas in those cells and I can't just delete them. That defeats the purpose. The file I uploaded had values, but the orginal has formulas.

2. it goes very slowly through the data. Since it is 10K rows, took a whole 5 mins to go through it all, way too long.

I don't think this is the best macro to go with. Thanks for helping.

[simeon.hruby]

No problem,

I think Bob Flanagan has the better solution anyway. I hope everything works out


Simeon

[Mile029]

Thank you Bob, that macro works beautifully. Only thing is that I want it to stop on the last blank row, so when it finds all blanks it stops, instead of lastR = to 50000, can I say lastR = blank on all columns?

[Bob Flanagan]

The statement

Please Login or Register  to view this content.

returns the last row with an entry. So, it doesn't run all the way to row 50000. If you add the following line:

Please Login or Register  to view this content.

that will tell you what row it is running to. Remove when not needed! The code does assume you will always have entries in column A.

Robert Flanagan
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

[Mile029]

giving me a debug error when I use it in my massive spreadsheet.

Stops on this line:
If Application.Round(Cells(I, C).Value, 2) <> 0 Then

I noticed when it get's to something in columns E-L that is not numerical (i.e. there is text in that cell) it gives that error. Works with blanks and all numbers, but not text. Can it skip over if not a number? meaning just leave it there, do not hide.

Or like I said above, can it just stop on the first blank line it encounters, this way it's not running into the next set of data which has new text headers in those columns, thus a non numerical value, thus the error.

[Bob Flanagan]

The following modification will check for error cells, blanks in all entris, and cells with text. If found such rows will not be hidden.

Please Login or Register  to view this content.

Robert Flanagan
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

[Mile029]

That's it! Thank you Bob, it is doing exactly what I want. Awesome work!