i need a macro for excel spread sheet?

Question

hi,
I need a method to automatically hide rows on certain Excel spreadsheets when the total value of the row is zero.

I am working on some files that has a lot of zero in many rows and columns. Rather than manually hiding each row that shows only zeros, can a macro be created that I could run to automatically detect the rows that total zero and hide them?  
I found a bit of info on the internet which suggested that this could be done with VBA code, but that's beyond my area of expertise.  
Can you help?

Thanks,

garbo7441 · Accepted Answer

This macro will hide all rows where a '0' is found in Column B.  If your Total column is not 'B' then change the 'B' in Line 3 of the macro to the letter of your Total column. Change both instances of 'B'

Open your workbook

Copy the modified macro to the clipboard 

Sub Hide_Zero_Rows()
Dim rng As Range
Set rng = Range("B1:" & Range("B65536"). _
End(xlUp).Address(0, 0))
For Each cell In rng
cell.Select
If ActiveCell.Value = "0" Then
ActiveCell.EntireRow.Hidden = True
End If
Next
End Sub

Next, press ALT  +  F11

Insert  >  Module

Paste the macro into the module space to the right.

Close back to Excel

Go to Tools  >  Macro  >  Macros

Highlight this macro, if it is not already highlighted.

Click:  Options

Select a letter to be used as a keyboard shortcut.

Close back to Excel.

Press CTRL  +  your letter to run the macro and hide the rows.

Anonymous · Answer

You don't even need a macro.

Format the cells as custom and then you can have three different formats, e.g.

#,##0.00;[Red]-#,##0.00;

This tells it to format with 2 decimal places for all values, negative values in red, and zeroes left blank.

Trending News

i need a macro for excel spread sheet?

2 Answers