Hide Rows if value is 0,

Re: Hide Rows if value is 0,

Two examples: The first code, placed in a general module, will hide all rows that have a zero in column A.

Option Explicit


Sub HideZeroRows()
Dim CheckNum As Long, LastRow As Long
LastRow = Range("A65536").End(xlUp).Row


For CheckNum = LastRow To 2 Step -1
If Cells(CheckNum, 1).Value = 0 Then Cells(CheckNum, 1).EntireRow.Hidden = True
Next CheckNum


End Sub

The following placed in the sheet's private module will hide a row whenever a zero in entered into column A in the range A2:A12

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("A2:A12")) Is Nothing Then
    If Target.Value = 0 Then Target.EntireRow.Hidden = True
End If
End Sub

Re: Hide Rows if value is 0,

What you will work, but it probably is overkill (overwork). Each time you select a different cell on the worksheet the Worksheet_SelectionChange will rerun the macro even if no data have changed anywhere on the worksheet -- much less in column A. That's why I used the Worksheet_Change event, which will fire only if a value in column A is changed. The following will act on all the rows when, but only when, a value in A1:A159 is changed.

Private Sub Worksheet_Change(ByVal Target As Excel.Range) 
Dim CheckNum As Long
    If Not Intersect(Target, Range("A2:A159")) Is Nothing Then 
    For CheckNum = 159 To 1 Step -1 
        If Cells(CheckNum, 1).Value = 0 Then Cells(CheckNum, 1).EntireRow.Hidden = True 
    Next CheckNum 
    End If 
End Sub

The question is which Event Macro to use to serve your needs. Perhaps you only need to execute when the sheet is first activated???

Re: Hide Rows if value is 0,

It just dawned on me that nothing in the macors we've discussed ever UNHIDES a row if the column A value changes from zero to something else. Perhaps the following would be good to consider.

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim CheckNum As Long
    Application.ScreenUpdating = False
    If Not Intersect(Target, Range("A2:A159")) Is Nothing Then
        For CheckNum = 159 To 1 Step -1
            If Cells(CheckNum, 1).Value = 0 Then
                Cells(CheckNum, 1).EntireRow.Hidden = True
            Else: Cells(CheckNum, 1).EntireRow.Hidden = False
            End If
        Next CheckNum
    End If
    Application.ScreenUpdating = True
End Sub

Re: Hide Rows if value is 0,

Hello out there!

I've been trying to re-work (changing C.EntireColumn.Hidden = C.Value = 0 to C.EntireRow.Hidden = C.Value = 0, etc.) the following code to HIDE COLUMNS where cells in the target range equal ZERO so that it will HIDE ROWS where cells in the target range (Named "UPC-SKU" or located at "A$22:A$141") equal ZERO.

Sub HideColumn()
    Dim rngTest As Variant, C As Range, i As Integer
    rngTest = Array("DENTotalFacings", "DOMTotalFacings", "EASTotalFacings", "GENTotalFacings", _
    "NORTotalFacings", "PHXTotalFacings", "PORTotalFacings", "RANTotalFacings", _
    "SEATotalFacings", "VONTotalFacings")
    For i = LBound(rngTest) To UBound(rngTest)
        For Each C In Range(rngTest(i))
            C.EntireColumn.Hidden = C.Value = 0
        Next C
    Next i
End Sub

So far I've been unsuccessful. Particularly, problems came up in the debugger with this line:

For Each C In Range(rngTest(i))

Any suggestions?

The goal is to have a macro tucked away inactive (standard module) until called up to run MANUALLY. I have used links to external files so that I can "Edit Links" to pull data from new files as necessary, but I want the freedom to see the file I publish to the field UNALTERED, before I hide un-used rows.

I don't understand VBA Code event timing well enough to decide which of the above examples would be best.

I appreciate any help you can offer!

THANKS!
James R. Smith

Re: Hide Rows if value is 0

Hi!

For anyone who may be interested, I found a solution for my own situation in the following code posted elsewhere in the forum.

The goal was a macro that could be called up manually to test a range as desired, rather than one that was contantly looking for a change to take place in the target cell range. I wanted to be able to hide rows AFTER editing, without having to worry about the macro hiding rows DURING the edit phase.

Sub HideRows()


Dim c
Application.ScreenUpdating = False


With Worksheets("CHECKLIST").Range("UPCSKU")
.EntireRow.Hidden = False
End With


For Each c In Worksheets("CHECKLIST").Range("UPCSKU")
If c.Value = 0 Then


c.EntireRow.Hidden = True
End If
Next c


ActiveSheet.DisplayAutomaticPageBreaks = False
Application.ScreenUpdating = True




End Sub

ENJOY!
JRSmith729