VBA Macro to Hide Rows Upon Entering New Data in Named Table Range

[powerpivotlegal]

Hello,

I am extremely limited in my knowledge of VBA macros and cannot seem to find the exact solution on searching through forum posts.

I am trying to write a macro that hides the row when you mark the cell in the last column of the named table range. For example, Table Name = "Ben" for Columns A2 through J20. If J4 is marked with an "X" then hide row 4. Hide row 5 if J5 is marked with an "x", etc.

This macro will need to be continuously running so that it incorporates any new rows of data added to the named table range.

The best I could cobble together is the macro below, but it runs super slow because I had to define the range so large in order to account for possible new row additions. How can a macro just look to the range of data in just one column of the named table range?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range, c As Range
Set r = Range("j2:j20000")
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
For Each c In r
If c = "X" Or c = "x" Then
c.EntireRow.Hidden = True
Else
c.EntireRow.Hidden = False
End If
Next c
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

Many thanks in advance.

Regards,
James

 

[My Aswer Is This]

So any time a "x" or "X" is placed in column "J" you want that row hidden.
Is that what you want?

 

[My Aswer Is This]

If the answers to my previous questions are true use this script:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("J:J")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Value = "x" Or Target.Value = "X" Then
Rows(Target.Row).Hidden = True
End If
End If
End Sub

 

[powerpivotlegal]

WOW! It works perfectly.

Knowing how picky the end-user is, is there an alternative macro that creates a button outside the named table range for the end-user to select and hide all entries of the named table range. Meaning they don't have to fill out Column J but instead click a button to hide the last or all completed rows of the table?

 

[My Aswer Is This]

And how do we know when this occurs?
Your quote:

"but instead click a button to hide the last or all completed rows of the table?"
Do you mean when every single range in the table is filled with some value.

 

[powerpivotlegal]

The end-user doesn't want to see all the rows of data after he/she adds a new row of data. I'm assuming the end-user will have filled out all the columns before they would click a button or a check box to hide the rows of data. Similar to marking the "x" in the last column to hide the row, they would instead just click a button to hide or unhide the completed row(s).