VBA Code Error - Reveal next line in multiple areas

tlc53

Active Member
Joined
Jul 26, 2018
Messages
365
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!


Code:
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
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,049
Office Version
  1. 2016
Platform
  1. Windows
It might go on one line with this :
Code:
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

Active Member
Joined
Jul 26, 2018
Messages
365
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,297
Office Version
  1. 365
Platform
  1. Windows
Try
Code:
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,297
Office Version
  1. 365
Platform
  1. Windows
You're welcome
 

Watch MrExcel Video

Forum statistics

Threads
1,108,954
Messages
5,525,880
Members
409,669
Latest member
JDCupps

This Week's Hot Topics

Top