VBA, trying to hide rows on different worksheet.

Hi,

I'm new to VBA so apologies for the below explanation.

I have two work sheets.
Let's call them;
- Sheet1
- Sheet2

In each worksheet is a dropdown list.
When the dropdown list is selected on either sheet I already have a macro which automatically runs to update the dropdown on the other sheet.

The dropdown changes the list of data shown on each sheet.
Sheet1 however ends up with lots of empty rows of data.

I'm therefore trying to get a macro to automatically hide all rows with no data on Sheet1

I've already managed to do this on Sheet1 however when I change the dropdown on the Sheet2 then Sheet1 updates the dropdown but the macro doesn't run to hide the rows.
I'm therefore trying to get the VBA code to work on Sheet2 so that it not only updates the dropdown on Sheet1 but it also hides the rows on Sheet1 at the same time.

The VBA code I have is as follows;

Sheet1 (working correctly; when dropdown changed then the dropdown on Sheet2 also changes & the macro hides the blank rows on Sheet1)

Private Sub Worksheet_Change(ByVal Target As Range)
Dim targetSheet As Worksheet
If Not Intersect(Target, Range("A3")) Is Nothing Then
Set targetSheet = ActiveWorkbook.Worksheets("Customer Overview")
On Error Resume Next
Application.EnableEvents = False
targetSheet.Range("A2") = Target.Value
Application.EnableEvents = True
Set targetSheet = ActiveWorkbook.Worksheets("Lifetime Account")
On Error Resume Next
Application.EnableEvents = False
targetSheet.Range("A3") = Target.Value
Application.EnableEvents = True
End If

Sheets("Aged Debtor").Select
ActiveSheet.Unprotect
'Updateby Extendoffice 20160913
Dim xRg As Range
Application.ScreenUpdating = False
For Each xRg In Range("A10:A43")
If xRg.Value = "" Then
xRg.EntireRow.Hidden = True
Else
xRg.EntireRow.Hidden = False
End If
Next xRg
Application.ScreenUpdating = True
Sheets("Aged Debtor").Select
ActiveSheet.protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

Sheet2 (not working; this changes the dropdown on Sheet1 correctly however doesn't hide the rows on Sheet1)

Private Sub Worksheet_Change(ByVal Target As Range)
Dim targetSheet As Worksheet
If Not Intersect(Target, Range("A3")) Is Nothing Then
Set targetSheet = ActiveWorkbook.Worksheets("Customer Overview")
On Error Resume Next
Application.EnableEvents = False
targetSheet.Range("A2") = Target.Value
Application.EnableEvents = True
Set targetSheet = ActiveWorkbook.Worksheets("Aged Debtor")
On Error Resume Next
Application.EnableEvents = False
targetSheet.Range("A3") = Target.Value
Application.EnableEvents = True
End If

Sheets("Aged Debtor").Select
ActiveSheet.Unprotect
With Worksheets("Aged Debtor")
'Updateby Extendoffice 20160913
Dim xRg As Range
Application.ScreenUpdating = False
For Each xRg In Range("A10:A43")
If xRg.Value = "" Then
xRg.EntireRow.Hidden = True
Else
xRg.EntireRow.Hidden = False
End If
Next xRg
Application.ScreenUpdating = True
Sheets("Aged Debtor").Select
ActiveSheet.protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Sheets("Lifetime Account").Select
End With
End Sub

Not sure what I have wrong in the 2nd sheet code?

Any help very much aprpeciated.

Hi Matthew,

The general rule is that macros have a range that they work on. If you have a macro behind a worksheet, it should only work on that sheet. If you want a macro to work on many different sheets you need to put it in a MODULE which looks over all sheets and userforms. The topic is called Scope.

https://www.stl-training.co.uk/artic...ba-coding.html

I'm not sure that is your problem but from what you suggest, it might be.

In the meantime, try modifying the last part of your sheet 2 code.
Replace this

Please Login or Register  to view this content.

With this

Please Login or Register  to view this content.

Originally Posted by matthew_salter

@JLGWhiz, that's worked like a dream!
Wonderful, thank you.

Thanks also to @MarvinP for looking into this as well.

You're welcome, and don't forget to mark the thread as solved. (Thread tools)