Hal Face - Hide Rows

Houstons_Time

New Member
Joined
Sep 6, 2007
Messages
13
If Target.Count > 11 Then Exit Sub
Dim ThisRow As Long
ThisRow = Target.Row
Sheets("Sheet1").Rows(ThisRow).Hidden = _
Application.WorksheetFunction.CountA(Rows(ThisRow).EntireRow) = 0

I have used your above code and works perfectly fine for one MASTER SHEET - but I am having problem when I try to use this code for TWO MASTER SHEETS.

I have several SLAVE SHEETS and TWO MASTER SHEETS. I have TWO TABLES in each SLAVE SHEET linked TWO MASTER SHEETS indivdually.

I need your help in figuring out a way, where If i edit TABLE 1, it should effect MASTER SHEET 1 (Meaning hide or display rows in Master Sheet).

Is there any way you can tweek this MACRO that you have given for me.

Thanks.
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
Sure, we should be able to do that.
For that I'll need to know what the ranges and sheet names are.
On the slave sheets, what range you want to work for MasterSheet1?
And what range do you want to work for MasterSheet2?
(And. . . are 'MasterSheet1' & 'MasterSheet2' the real names for those sheets?)

Here's an example that will work on MasterSheet1 when rows 2 through 10 get changed
and MasterSheet2 when rows 15 through 25 get changed in the slave sheets.
(This code needs to go in the sheet module of all slave sheets.)
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub

Dim ThisRow As Long
ThisRow = Target.Row

If ThisRow > 1 And ThisRow < 11 Then
  Sheets("MasterSheet1").Rows(ThisRow).Hidden = _
  Application.WorksheetFunction.CountA(Rows(ThisRow).EntireRow) = 0
End If

If ThisRow > 14 And ThisRow < 26 Then
  Sheets("MasterSheet2").Rows(ThisRow).Hidden = _
  Application.WorksheetFunction.CountA(Rows(ThisRow).EntireRow) = 0
End If

End Sub

Hope it helps. Let us know if you need a hand tweaking it to suit.
(And sorry for the delay. Had a problem with my router/switch.) :devilish:
 

Houstons_Time

New Member
Joined
Sep 6, 2007
Messages
13
Thanks a million for your quick response... Quiet appreciated, yet again I turn back to you with another issue.

The main issue is that - When I use this Macro for Test Purposes on a DUMMY NEW SHEET, this works perfectly fine, but yet again - when I try to use this macro on a REAL SHEET - This macro dosent work for some wiered reason. Is there some thing wrong on my REAL SHEET.

Hope I am not bothering you with too many questions..

Also, my last question to you would be...

Is there a possibility of Linking TWO CELLS and not letting the LINK BREAK even if you change the DATA in both of those TWO CELLS.

Instance - A1 is linked to B1 (I would like to be able to change the information on either of the cells, but not let the link break) - Any idea's on this...

Once again a million thanks are not enough for me to say - how greatful I am to you...

Thanks
 

Houstons_Time

New Member
Joined
Sep 6, 2007
Messages
13
Thanks a million for your quick response... Quiet appreciated, yet again I turn back to you with another issue.

The main issue is that - When I use this Macro for Test Purposes on a DUMMY NEW SHEET, this works perfectly fine, but yet again - when I try to use this macro on a REAL SHEET - This macro dosent work for some wiered reason. Is there some thing wrong on my REAL SHEET.

Hope I am not bothering you with too many questions..

Also, my last question to you would be...

Is there a possibility of Linking TWO CELLS and not letting the LINK BREAK even if you change the DATA in both of those TWO CELLS.

Instance - A1 is linked to B1 (I would like to be able to change the information on either of the cells, but not let the link break) - Any idea's on this...

Once again a million thanks are not enough for me to say - how greatful I am to you...

Thanks
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454

ADVERTISEMENT

No bother at all... It's what we're here for. :biggrin:
(And thanks for the kind words.)

Is there a possibility of Linking TWO CELLS and not letting the LINK BREAK even if you change the DATA in both of those TWO CELLS.

Instance - A1 is linked to B1 (I would like to be able to change the information on either of the cells, but not let the link break) - Any idea's on this...
I'm not sure I completely follow. What do you mean by 'linked'?
The only thing that comes to mind is you have some formulea in both of these cells, and
when one changes, the other calculates and changes its result.
Am I correct in thinking that if one changes, (and therefore the other changes), you
want to hide/unhide both rows on the appropriate Master Sheet?
If that's the case, I'd have to think about how that might be done as nothing's coming
to mind right off.
In either case, if you have a formula somewhere you can not overwrite it with other data
without losing the formula.

Am I even close to understanding what you mean?
 

Houstons_Time

New Member
Joined
Sep 6, 2007
Messages
13
ummm! Halface, is there a way where I can send you the Excel Sheet and you can have a look at this issue.

The problem is - the code you have given me for hiding and unhiding rows, does not seem to be working on my REAL SHEETS.

Your help will be grealy appreciated..
 

Houstons_Time

New Member
Joined
Sep 6, 2007
Messages
13
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub

Dim ThisRow As Long
ThisRow = Target.Row
If ThisRow > 52 And ThisRow < 73 Then
Sheets("Material's Master").Rows(ThisRow).Hidden = _
Application.WorksheetFunction.CountA(Rows(ThisRow).EntireRow) = 0
End If

If ThisRow > 5 And ThisRow < 48 Then
Sheets("Service's Master").Rows(ThisRow).Hidden = _
Application.WorksheetFunction.CountA(Rows(ThisRow).EntireRow) = 0
End If
End Sub

I have used this code - but is not working on the Master Sheet.. Any help will be greatly appreciated...

Thanks
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
Well, I don't normally do this but check your PMs for my email address and I'll
take a look and see what I can figure out.
Might not happen until later today (maybe tomorrow), I have some work to do on
my boat this afternoon.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,842
Messages
5,598,396
Members
414,235
Latest member
sucosama

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top