Automatically hide/unhide row by calling module

Xaba · Sep 7th 2020

Hi,

In another thread, which is unfortunately more than 365 days old, I found a code to automatically hide/unhide rows with a VBA code. When I implement this code in my sheet it works like a charm. See the code below:

Code

Private Sub Worksheet_Change(ByVal Target As Range)
'// Ignore if more than 1 cell changes
If Target.Cells.Count <> 1 Then Exit Sub
'// If Column E, rows 14 to 24 changed
If Not Intersect(Target, Range("E14:E24")) Is Nothing Then
'// If it's an even row ( = 1 for Odd rows)
If Target.Row Mod 2 = 0 Then
Target.Offset(2).EntireRow.Hidden = (Target.Value = vbNullString)
End If
End If
End Sub

Display More

I have multiple sheets. Now, I have implemented the code above in a module so that I can call the module with the active sheet. When the range in the sheets change I can easily change it in the module instead of changing it in every sheets code.

I call the module with the code below:

Code

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("E:E")) Is Nothing Then
Call HideUnhide
End If
End Sub

It calls the module but the code in the module doesn't work. It only seems to work when directy in the sheets code.

How can I adjust it to work?

Kind regards.

royUK · Sep 7th 2020

Why do that? You can have the code work in each sheet of the WorkBook if you use the WorkBook SheetChange event.

Is it the same range on all sheets?

Xaba · Sep 7th 2020

Dear RoyUK,

Thank you for your reply.

I want to do this because it could be that in the future the range where it applies to could change.

The range is the same in every sheet.

Kind regards,

royUK · Sep 7th 2020

I've amended the code slightly and placed it in the WorkBook module using the SheetChange event. It will now work on whichever sheet is active

Xaba · Sep 8th 2020

This does the trick!

I will now be using this code. Thank you for your help.

Kind regards,

royUK · Sep 8th 2020

Pleased to help

Xaba · Sep 9th 2020

Hi Roy,

I am now trying to expand the range (I've added E34:E44 and E54:E64). See the code below:

Code

Option Explicit
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
With Target
'// Ignore if more than 1 cell changes
If .Cells.Count <> 1 Then Exit Sub
'// If Column E, rows 14 to 24 changed
If Not Intersect(Target, Sh.Range("E14:E24", "E34:E44", "E54:E64")) Is Nothing Then
'// If it's an even row ( = 1 for Odd rows)
If .Row Mod 2 = 0 Then
.Offset(2).EntireRow.Hidden = .Value = vbNullString
End If
End If
End With
End Sub

Display More

This, however, doesn't work and I don't know why. How should the code be adjusted if I want to expand the range?

Kind regards,

royUK · Sep 9th 2020

You've put the code in the worksheet_change event, Not the WorkBook event.

Double click on THisWorkBook in the VB Editor's VBA Project section. In the left hand drop down make sure WorkBook is selected, then scroll down to SheetChange in the right hand drop down

Xaba · Sep 9th 2020

WorkBook and SheetChange are both selected. They were already selected, but it is still not working.

I get the following error:

Error run-time ‘450’

Wrong number of arguments or invalid property assignment

I don’t really know how to solve this.

royUK · Sep 9th 2020

This is from the code that you posted last

Code

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Xaba · Sep 9th 2020

That is indeed the code that I posted last. It's the code that I copied from your excel file. The only thing I did in your excel file is add the range E34:E44 and E54:E64. That, unfortunately, doesn't seem to work. I'm trying to figure out why?

royUK · Sep 9th 2020

Sorry, I should have asked where you had put the code.

Can you attach the workbook

royUK · Sep 9th 2020

Try this

Code

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
With Target
'// Ignore if more than 1 cell changes
If .Cells.Count <> 1 Then Exit Sub
'// If Column E, rows 14 to 24 changed
If Not Intersect(Target, Sh.Range("E14:E24, E34:E44, E54:E64")) Is Nothing Then
'// If it's an even row ( = 1 for Odd rows)
If .Row Mod 2 = 0 Then
.Offset(2).EntireRow.Hidden = .Value = vbNullString
End If
End If
End With
End Sub

Display More

Xaba · Sep 9th 2020

Now the code is correct.

Thank you for you continuous patience and quick reply's.

I can continue now.

Kind regards,

royUK · Sep 9th 2020

No problem. Did you notice the difference in writing the range references?

Xaba · Sep 9th 2020

Yes I did notice the difference. There's only the quotation marks at the beginning and end of the total range. Before I had it with every range.

royUK · Sep 9th 2020

That was the problem.

Xaba · Sep 16th 2020

Dear RoyUK,

I have an additional question, hope you know how to solve it.

The code unhides a row when the cell above that row is contains a value. It hides the row again when the cell is empty.

How can I adjust the code so that it only unhides the row?

To be clear, I don't want the row to be hidden again when the cell is empty.

Kind regards,

royUK · Sep 16th 2020

Try this

Code

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
With Target
'// Ignore if more than 1 cell changes
If .Cells.CountLarge <> 1 Then Exit Sub
'// If Column E, rows 14 to 24 changed
If Not Intersect(Target, Sh.Range("E14:E24, E34:E44, E54:E64")) Is Nothing Then
'// If it's an even row ( = 1 for Odd rows)
If .Row Mod 2 = 0 Then .Offset(2).EntireRow.Hidden = True
End If
End With
End Sub

Display More

Xaba · Sep 17th 2020

Thank you for your suggestion.

What it does with this code is hide the row when I fill in the cell above. It does not unhide the rows.

To be more specific:

When I fill in cell E14 I want it to unhide row 16. Then when I fill in cel E16 I want it do unhide row 18, and so on. When I then empty cell E14 (or E16) I want it to still keep row 16 (and 18 in this case) visible, instead of hiding it again (like it is doing now).

I hope I am clear.

Is this possible?

Kind regards,

Automatically hide/unhide row by calling module

Share

Tags