Hide Rows Depending on Values in Column

Hi Everyone,

How can I hide rows depending on the value in a column. I have a spreadsheet where column A is calculated as the sum of columns B to G. If this value is 0 I want to hide the row. From searching online I have found the following but cant get it to work:

---------------------------

Option Explicit

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

---------------------------

As a complete beginner to Macros where do I put this? I have tried right clicking the Tab, selecting view code, and pasting it into the screen. This hasn't worked. Do I need to do something to activate or run it? Can the code be set up so it constantly runs, so whenever a value on the sheet is changed rows will automatically be hidden or unhidden?

Thanks,

Ger

Event macros need to be placed in the module that corresponds to the object they refer to. In the case of the code you pasted, it will need to go on the "worksheet" module that corresponds to the sheet you want it to work in. To do this:

Open VBA screen (Alt+F11)
Click on the + next to VBAProject(YourWorkbookName)
Double click on Sheet#(YourSheetName)
Paste your code there.

I assume you understand that the code you pasted will check every time a cell is changed and will hide all the rows according to the value, but only if what is changed is in column A and the value is 0. What I mean is it won't check the entire sheet, only the row that was changed. And only if the changed happened on column A.

Try with this code:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Ws As Worksheet
    Dim RowCheck As Long
    
    Set Ws = ActiveSheet
    
    Application.ScreenUpdating = False
    With Ws
        For RowCheck = 1 To .Cells(.Rows.Count, 1).End(xlUp).Row
            If .Cells(RowCheck, 1) = 0 Then
                .Cells(RowCheck, 1).EntireRow.Hidden = True
            Else
                .Cells(RowCheck, 1).EntireRow.Hidden = False
            End If
        Next RowCheck
    End With
    Application.ScreenUpdating = True
End Sub

Just a note: this code will run with ANY change made on your sheet. If you have a lot of information on it, it might make your machine slow. I would advice to place a button with a macro attached to it so that the user clicks on it before using the data on the sheet, instead of using an event macro like this. Of course, that is my point of view and you might think you want it like this, I just wanted to tell you beforehand so you don't see any surprises.

That will help, yes. Change the macro to this if that's what you want:

Option Explicit

Private Sub Worksheet_Activate()
    Dim Ws As Worksheet
    Dim RowCheck As Long
    
    Set Ws = ActiveSheet
    
    Application.ScreenUpdating = False
    With Ws
        For RowCheck = 1 To .Cells(.Rows.Count, 1).End(xlUp).Row
            If .Cells(RowCheck, 1) = 0 Then
                .Cells(RowCheck, 1).EntireRow.Hidden = True
            Else
                .Cells(RowCheck, 1).EntireRow.Hidden = False
            End If
        Next RowCheck
    End With
    Application.ScreenUpdating = True
End Sub

No that won't work, you need to have two separate macros for it. You would have to add something like this:

Option Explicit

Private Sub Worksheet_Activate()
    Dim Ws As Worksheet
    Dim RowCheck As Long
    
    Set Ws = ActiveSheet
    
    Application.ScreenUpdating = False
    With Ws
        For RowCheck = 1 To .Cells(.Rows.Count, 1).End(xlUp).Row
            If .Cells(RowCheck, 1) = 0 Then
                .Cells(RowCheck, 1).EntireRow.Hidden = True
            Else
                .Cells(RowCheck, 1).EntireRow.Hidden = False
            End If
        Next RowCheck
    End With
    Application.ScreenUpdating = True
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Ws As Worksheet
    Dim RowCheck As Long
    
    Set Ws = ActiveSheet
    If Not Intersect(Target, Range("C3")) Is Nothing Then
        Application.ScreenUpdating = False
        With Ws
            For RowCheck = 1 To .Cells(.Rows.Count, 1).End(xlUp).Row
                If .Cells(RowCheck, 1) = 0 Then
                    .Cells(RowCheck, 1).EntireRow.Hidden = True
                Else
                    .Cells(RowCheck, 1).EntireRow.Hidden = False
                End If
            Next RowCheck
        End With
        Application.ScreenUpdating = True
    End If
End Sub

Since there are two different events involved, you need two separate macros for it. Each event macro works for only one event. (event is the action that triggers it "select the tab", in fact activate the sheet, or "change C3")

Make this change:

Option Explicit

Private Sub Worksheet_Activate()
    Dim Ws As Worksheet
    Dim RowCheck As Long
    Set Ws = ActiveSheet
    Application.ScreenUpdating = False
    With Ws
        For RowCheck = 1 To .UsedRange.Rows.Count
            If .Cells(RowCheck, 1).Value = 0 And .Cells(RowCheck, 1).Value <> "" Then
                .Cells(RowCheck, 1).EntireRow.Hidden = True
            Else
                .Cells(RowCheck, 1).EntireRow.Hidden = False
            End If
        Next RowCheck
    End With
    Application.ScreenUpdating = True
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Ws As Worksheet
    Dim RowCheck As Long
    
    Set Ws = ActiveSheet
    If Not Intersect(Target, Range("C1")) Is Nothing Then
        Application.ScreenUpdating = False
        With Ws
            For RowCheck = 1 To .UsedRange.Rows.Count
                If .Cells(RowCheck, 1).Value = 0 And .Cells(RowCheck, 1).Value <> "" Then
                    .Cells(RowCheck, 1).EntireRow.Hidden = True
                Else
                    .Cells(RowCheck, 1).EntireRow.Hidden = False
                End If
            Next RowCheck
        End With
        Application.ScreenUpdating = True
    End If
End Sub

The problem was that apparently End(xlUp), which is intended to find the last non empty cell, will only look at cells that are not hidden, so if the one you intended to show is the last on your list it wouldn't see it. I didn't know that lol. Also I added a condition, that the cell is not empty when hiding, because before that it was seeing empty cells as being equal to 0. So I changed both macros. (I marked the changes made in blue)