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:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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)