VBA: active.sheet hide rows and clear contents of cells

[ExtraCheese]

I'm using two subs to hide rows when a value in column D is 0.
Another sub is used to clear the contents of a single cell in column J within the hidden row (I need to keep the other data within that row).
Both subs below:

Private Sub Worksheet_Activate()
For Each c In Range("D2:D2000")
If c.Value = 0 Then
Rows(c.Row).Hidden = True
Else
Rows(c.Row).Hidden = False
End If
Next
End Sub

Private Sub Worksheet_Activate(ByVal Target As Range)
If Not Intersect(Target, Range("D2:D2000")) Is Nothing Then
If Target = 0 Then
Range("J" & Target.Row).ClearContents
End If
End If
End Sub

However, excel won't let me use worksheet_activate both times. Is there any way I can merge the two subs so that both will be executed when the worksheet is activated?

 

[Joe4]

Welcome to the Board!

I am not sure what you are trying to do in your second one. The "Worksheet_Activate" event procedure does not have a Target argument.
Perhaps you should be using a "Worksheet_SelectionChange" or "Worksheet_Change" event procedure there (both of those use "Target")?
I guess it depends on what/when you want to trigger that code to run.

 

[ExtraCheese]

Hi Joe4! Thank you for the warm welcome.

Sheet1 contains a list of all possible articles. An order is put in sheet 1, where sheet 2 will create a delivery schedule.
Sheet 2 is where the code will be used. Sheet 2 contains some Index and Vlookup formula's so it will be updated regularly.

The goal of the VBA code is to hide all the rows where the amount in column "D" is 0 -> First sub
The final user will input a delivery nr. in column "J", but this number should be removed when the delivery is finished (the 0 in column D). -> Second sub

Perhaps I use an unorthodox way of achieving this, but the both subs work as they should individually, but not together...
The code should be activated when the sheet is accessed, so by worksheet_activate.

Any help is much appreciated.

 

[Joe4]

Does this do what you want?

Private Sub Worksheet_Activate()

    Dim c As Range

    For Each c In Range("D2:D2000")
        If c.Value = 0 Then
            Cells(c.Row, "J").ClearContents
            Rows(c.Row).Hidden = True
        Else
            Rows(c.Row).Hidden = False
        End If
    Next
    
End Sub

If not, please describe a situation in which it does not work.

 

[ExtraCheese]

Excellent, this works like a charm. Thank you very much!

 

[Joe4]

You are welcome.
Glad I was able to help!