Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can you create a macro to hide rows with zero values?
I am trying to automate emailing of financial reports to managers in our
Organization. I have one spreadsheet template with every account listed. However, not every manager has amount in every line, and the report is pretty long with every account listed. I would like to create a macro to look at the a range of values in each row of the report, and then hide any rows which have a zero value in each cell in the range. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can you create a macro to hide rows with zero values?
Hi natrujillo12
You can use this to hide all the cells with a 0 in column A in the first 100 rows of the active sheet. Sub UnionExample() Dim Lrow As Long Dim CalcMode As Long Dim StartRow As Long Dim EndRow As Long Dim rng As Range With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With With ActiveSheet .DisplayPageBreaks = False StartRow = 1 EndRow = 100 For Lrow = StartRow To EndRow Step 1 If IsError(.Cells(Lrow, "A").Value) Then 'Do nothing, This avoid a error if there is a error in the cell ElseIf .Cells(Lrow, "A").Value = "0" Then If rng Is Nothing Then Set rng = .Cells(Lrow, "A") Else Set rng = Application.Union(rng, .Cells(Lrow, "A")) End If End If Next End With 'hide all rows in one time If Not rng Is Nothing Then rng.EntireRow.Hidden = True With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "natrujillo12" wrote in message ... I am trying to automate emailing of financial reports to managers in our Organization. I have one spreadsheet template with every account listed. However, not every manager has amount in every line, and the report is pretty long with every account listed. I would like to create a macro to look at the a range of values in each row of the report, and then hide any rows which have a zero value in each cell in the range. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to hide rows with zero values in columns | Excel Discussion (Misc queries) | |||
Macro code to hide rows and not calculate hidden rows | Excel Discussion (Misc queries) | |||
Using Macro to Hide Rows with Certain Values | Excel Discussion (Misc queries) | |||
Automatically hide rows with no values | Excel Discussion (Misc queries) | |||
Can anyone tell me how to hide rows that have 0 values and text. | Charts and Charting in Excel |