3

I have a set of raw data on sheet 1 of my workbook. On sheet 2 I use formulas to pull in some of that data from sheet 1.

Using a Macro that I created and posted in Module 1 I want to hide any rows that do not contain specific data. I am able to execute the macro directly when I need to via Run>Run Sub/Userform. It works perfectly.

However, I would prefer it to run when it needs to update via a worksheet_change event in the background whenever an edit is made to sheet 1. Because I am making edits on sheet 1 but want the change to execute the macro on sheet 2 I understand that the worksheet_change event has to be placed in "This Worksheet" as opposed to the specific sheet.

Here's the macro code

Sub HideRows()
Dim i As Integer
i = 1
Do While Not Cells(i, 5) = ""
    If Cells(i, 5).Value = 0 Then
        Rows(CStr(i) + ":" + CStr(i)).EntireRow.Hidden = True
    ElseIf Cells(i, 5).Value <> 0 And Rows(CStr(i) + ":" + CStr(i)).EntireRow.Hidden = True Then
        Rows(CStr(i) + ":" + CStr(i)).EntireRow.Hidden = False
    End If
i = i + 1
Loop
End Sub

Run directly the code above does what I need. The code I am using below to execute this via a worksheet_change event doesn't work.

Private Sub Worksheet_Change(ByVal Target As Range)
    With Me.Worksheets("Sheet2")
        Call HideRows
    End With
End Sub

Any help with how to execute the macro using worksheet_change would be appreciated.

0m3r
  • 12,286
  • 15
  • 35
  • 71
Andy
  • 1,422
  • 5
  • 27
  • 43
  • Isn't this the same as your prev [Question](http://stackoverflow.com/questions/32542758/hide-unhide-cells-with-empty-rows-on-worksheet-change) – Siddharth Rout Sep 13 '15 at 19:58
  • Not really, that was about the code needed to hide/unhide rows. This is about how to execute that. There is some overlap though but I didn't post any code in that question. – Andy Sep 13 '15 at 20:02
  • 1
    in your `HideRows` macro, fully qualify the cells. For example `Sheet2.Cells(i, 5).Value` – Siddharth Rout Sep 13 '15 at 20:03
  • 1
    Also where is the change happening in Sheet 1? Which Cell or Column? – Siddharth Rout Sep 13 '15 at 20:07
  • Thanks for the reply. Changes are made on sheet 1, column A. The formulas that pull that data into sheet 2 are in column E. Column E is where the HideRows macro is looking for a "0". – Andy Sep 13 '15 at 20:09
  • 1
    I have posted an answer. You may have to refresh the page to see it – Siddharth Rout Sep 13 '15 at 20:16

1 Answers1

2

Few points worth noting

  1. The problem is that you are not fully qualifying the cells so when the HideRows macro is called, even though you have used With Me.Worksheets("Sheet2") it is still referring to the current sheet which is Sheet1. Fully qualify your range objects as shown below. Notice the Dots before them?

  2. If the changes are happening in Col A of Sheet1 then trap that else your macro will run for any change in Sheet1 thereby making your workbook slow.

  3. You do not need to keep the macro in a module. You can put the entire code in Sheet1 as shown below

  4. Also Rows(CStr(i) + ":" + CStr(i)) can be written as Rows(i)

  5. When working with rows in Excel it is always advisable to declare them as Long and not Integer. Post Excel2007 the number of rows have increased and the Integer variable may not be able to accommodate that.

Is this what you are trying? Put this code in Sheet code area of Sheet1

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim i As Long, lRow As Long

    '~~> Check of the change is happening in Col 1
    If Not Intersect(Target, Columns(1)) Is Nothing Then
        With Worksheets("Sheet2")
            lRow = .Range("E" & .Rows.Count).End(xlUp).Row

            For i = 1 To lRow
                If .Cells(i, 5).Value = 0 Then
                    .Rows(i).EntireRow.Hidden = True
                ElseIf .Cells(i, 5).Value <> 0 And .Rows(i).EntireRow.Hidden = True Then
                    .Rows(i).EntireRow.Hidden = False
                End If
            Next i
        End With
    End If
End Sub
Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
  • Thanks. I have posted this code into the code area of Sheet 1 but am seeing the 0 in the relevant cell but the row is not being hidden. I will check to make sure I'm not doing something wrong. – Andy Sep 13 '15 at 20:23
  • 1
    BTW you can change `If .Cells(i, 5).Value = 0 Then` to `If .Cells(i, 5).Value = 0 And .Rows(i).EntireRow.Hidden = False Then` – Siddharth Rout Sep 13 '15 at 20:29
  • Thanks for the help. I've created a whole new sheet and still can't get it to hide the relevant row. Not sure what I am doing wrong but I'll keep trying... – Andy Sep 13 '15 at 20:35
  • 1
    Ok do this... `1` Ensure that the code is pasted in the "Sheet1" code area `2` I hope you have got the sheet names correct? `3` In the Immediate window of VBE, type this `Application.Enableevents=true` – Siddharth Rout Sep 13 '15 at 20:37
  • Wow, 1 and 2 were fine but it still didn't work. I did 3 and it worked. I have plenty of other macros in other sheets working fine without #3 so I need to do some reading about that, haha. – Andy Sep 13 '15 at 20:44
  • Many, many thanks for your time and effort. You're a credit to this community!! – Andy Sep 13 '15 at 20:45
  • 1
    Glad to be of help :) You may have switched it off in some other macro by mistake and forgot to switch it on. If you would have given a serious thought to what I suggested in the first comment of your previous question then you wouldn't have faced this problem ;) There is a link that I gave you to read in that comment ;) – Siddharth Rout Sep 13 '15 at 20:47
  • Here is the [LINK](http://stackoverflow.com/questions/32542758/hide-unhide-cells-with-empty-rows-on-worksheet-change) again :) – Siddharth Rout Sep 13 '15 at 20:49