unhide row simultaneously

**gt40** · 09-14-2008, 05:35 AM

Hi guys,

I have a simple code for hiding rows when the value in column I is 0. however i want it to unhide the next row when data is entered in the current row. so if for example row 3 is the last unhiden row at the moment, row 4 will unhide once i enter data in row 3. i currently have row G set to show 1 when the current row has data and for rows i permanently want displayed, and column H set to show a 1 when the previous row has row has data, and zero if it does not.

The code works but not as i am entering data... it only updates (unhides rows if i go to another sheet and then come back again). Any ideas on how to get it to update as i enter data in rows??

Thnx in advance

**broro183** · 09-14-2008, 05:44 AM

hi,

Try using a selection change macro eg:

Please Login or Register  to view this content.

It's often helpful if you post your existing code with the question. If my suggestion doesn't help (or even if it does), can you please post your existing code to help people make more informed suggestions/improvements?

hth
Rob

**gt40** · 09-14-2008, 05:53 AM

Hi Rob,

thnx for the help. but i'm not to sure what u mean with the selection change macro... sorry i'm a novice at macros. One other thing i have noticed is that the macro takes a while to update (like 10 secs) is this normal or could i be doing something wrong. i have pasted the code into the appropriate sheet in Visual Basic. Should i be posting it into one of the modules?

Please Login or Register  to view this content.

**broro183** · 09-14-2008, 07:33 AM

hi,
Sorry, this reply may leave more questions than answers as I'm off to bed now - I'll check this thread out again tomorrow...

1) You can delete "Private Sub Worksheet_Activate()" & replace it with "Private Sub Worksheet_Change(ByVal Target As Range)". This is slightly different than what I previously suggested & will run each time you change something on the sheet. Although, you may find the 10 second time (effectively caused by the 1-1000 repetition/loop) is way too long when used in this way.
Recording this in the sheet code is appropriate because the actions of the macro relate to what happens on the specific sheet.

2) To help with the speed I suggest using a different approach which involves the use use of autofiltering rather than looping. I have included a commented section which could be uncommented & used for clearing any active filters before making these changes.

Please Login or Register  to view this content.

hth
Rob

**gt40** · 09-14-2008, 07:43 PM

Thnx for that Rob,

i had tried the selection change macro at just before your last post, and although it update the spreadsheet as i enetred data, it virtually made it unusable with the sheet taking 10-25 seconds to update after every line.

i will try your suggestion later today and post back the outcome.

thnx

**gt40** · 09-15-2008, 06:42 AM

thanx v much. It worked a treat.... much appreciated.

**broro183** · 09-16-2008, 02:10 AM

Pleased I could help - thanks for the feedback :-)

btw, I forgot to mention this in my last post...
It's been recommended to me in the past that it is better if function code is put in a normal module & only macros for the sheet are included in the Sheet object in the VBE.

Rob