VBA Code Error - Reveal next line in multiple areas

[tlc53]

Hi there,

I use the below code to automatically reveal a row as the user enters details. It's been working fine but in this case, there are many ranges to take into account and when I tried to put it all under one, it came back with a syntax error (it's too long). Therefore, I am trying to break it into two, as below. However, I'm not quite sure how/if it can be done.
Can someone help me fix this VBA code please?
Thank you!

Private Sub Worksheet_Change(ByVal Target As Range)


Dim Rng As Range
Set Rng = Intersect(Target, [B21:B29,I21:I29,B47:B55,I47:I55,B73:B81,I73:I81,B99:B107,I99:I107,B125:B133,I125:I133,B157:B161,I157:I161,B175:B184,I175:I184,B191:B199,I191:I199,B221:B225,I221:I225,B239:B248,I239:I248])
If Not Rng Is Nothing Then Rng(2, 1).EntireRow.Hidden = False


End If


Dim Rng As Range
Set Rng = Intersect(Target, [B225:B263,I225:I263,B285:B289,I285:I289,B303:B312,I303:I312,B319:B327,I319:I327,B349:B353,I349:I353,B367:B376,I367:I376,B383:B391,I383:I391,B413:B417,I413:I417,B431:B440,I431:I440,B447:B455,I447:I455])
If Not Rng Is Nothing Then Rng(2, 1).EntireRow.Hidden = False


End Sub

 

[footoo]

It might go on one line with this :

Dim Rng As Range
Set Rng = Intersect(Target, Intersect([B:B,I:I], [21:29,47:55,73:81,[COLOR=#ff0000]ETC][/COLOR])) [COLOR=#ff0000]'Replace ETC with the rest of the row refs[/COLOR]
If Not Rng Is Nothing Then Rng(2, 1).EntireRow.Hidden = False

 

[tlc53]

Thanks footoo. It was a good idea. I thought it would work but unfortunately not. It's coming back with Run time error 424, object required.

 

[Fluff]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Rng As Range
   Set Rng = Intersect(Target, [B21:B29,I21:I29,B47:B55,I47:I55,B73:B81,I73:I81,B99:B107,I99:I107,B125:B133,I125:I133,B157:B161,I157:I161,B175:B184,I175:I184,B191:B199,I191:I199,B221:B225,I221:I225,B239:B248,I239:I248])
   If Not Rng Is Nothing Then Rng(2, 1).EntireRow.Hidden = False
   Set Rng = Intersect(Target, [B225:B263,I225:I263,B285:B289,I285:I289,B303:B312,I303:I312,B319:B327,I319:I327,B349:B353,I349:I353,B367:B376,I367:I376,B383:B391,I383:I391,B413:B417,I413:I417,B431:B440,I431:I440,B447:B455,I447:I455])
   If Not Rng Is Nothing Then Rng(2, 1).EntireRow.Hidden = False
End Sub

 

[tlc53]

Thanks Fluff!!

 

[Fluff]

You're welcome