simplify the VBA code

[srikanth sare]

If Range("$AF$35").Value = "NO" Then
   Sheet5.Range("AE:AE,BG:BG").EntireColumn.Hidden = True
   Else
   Sheet5.Range("AE:AE,BG:BG").EntireColumn.Hidden = False
End If

If Range("$AF$36").Value = "NO" Then
  Sheet5.Range("AF:AF,BH:BH").EntireColumn.Hidden = True
  Else
  Sheet5.Range("AF:AF,BH:BH").EntireColumn.Hidden = False
End If
 
If Range("$AF$37").Value = "NO" Then
  Sheet5.Range("AG:AG,BI:BI").EntireColumn.Hidden = True
  Else
  Sheet5.Range("AG:AG,BI:BI").EntireColumn.Hidden = False
End If
 
If Range("$AF$38").Value = "NO" Then
  Sheet5.Range("AH:AH,BJ:BJ").EntireColumn.Hidden = True
  Else
  Sheet5.Range("AH:AH,BJ:BJ").EntireColumn.Hidden = False
End If
 
If Range("$AF$39").Value = "NO" Then
  Sheet5.Range("AI:AI,BK:BK").EntireColumn.Hidden = True
  Else
  Sheet5.Range("AI:AI,BK:BK").EntireColumn.Hidden = False
End If
     
If Range("$AF$40").Value = "NO" Then
  Sheet5.Range("AJ:AJ,BL:BL").EntireColumn.Hidden = True
  Else
  Sheet5.Range("AJ:AJ,BL:BL").EntireColumn.Hidden = False
End If

If Range("$AF$41").Value = "NO" Then
   Sheet5.Range("AK:AK,BM:BM").EntireColumn.Hidden = True
   Else
   Sheet5.Range("AK:AK,BM:BM").EntireColumn.Hidden = False
End If

If Range("$AF$42").Value = "NO" Then
  Sheet5.Range("AL:AL,BN:BN").EntireColumn.Hidden = True
  Else
  Sheet5.Range("AL:AL,BN:BN").EntireColumn.Hidden = False
End If
 
If Range("$AF$43").Value = "NO" Then
  Sheet5.Range("AM:AM,BO:BO").EntireColumn.Hidden = True
  Else
  Sheet5.Range("AM:AM,BO:BO").EntireColumn.Hidden = False
End If
 
If Range("$AF$44").Value = "NO" Then
  Sheet5.Range("AN:AN,BP:BP").EntireColumn.Hidden = True
  Else
  Sheet5.Range("AN:AN,BP:BP").EntireColumn.Hidden = False
End If
 
If Range("$AF$45").Value = "NO" Then
  Sheet5.Range("AO:AO,BQ:BQ").EntireColumn.Hidden = True
  Else
  Sheet5.Range("AO:AO,BQ:BQ").EntireColumn.Hidden = False
End If
     
If Range("$AF$46").Value = "NO" Then
  Sheet5.Range("AP:AP,BR:BR").EntireColumn.Hidden = True
  Else
  Sheet5.Range("AP:AP,BR:BR").EntireColumn.Hidden = False
End If

If Range("$AF$47").Value = "NO" Then
  Sheet5.Range("AQ:AQ,BS:BS").EntireColumn.Hidden = True
  Else
  Sheet5.Range("AQ:AQ,BS:BS").EntireColumn.Hidden = False
End If
 
If Range("$AF$48").Value = "NO" Then
  Sheet5.Range("AR:AR,BT:BT").EntireColumn.Hidden = True
  Else
  Sheet5.Range("AR:AR,BT:BT").EntireColumn.Hidden = False
End If
 
If Range("$AF$49").Value = "NO" Then
  Sheet5.Range("AS:AS,BU:BU").EntireColumn.Hidden = True
  Else
  Sheet5.Range("AS:AS,BU:BU").EntireColumn.Hidden = False
End If
 
If Range("$AF$50").Value = "NO" Then
  Sheet5.Range("AT:AT,BV:BV").EntireColumn.Hidden = True
  Else
  Sheet5.Range("AT:AT,BV:BV").EntireColumn.Hidden = False
End If

 

[Peter_SSs]

When posting vba code, please use code tags to preserve the formatting. My signature block below has more information. I have fixed it for you this time.

Try this with a copy of your workbook

Sub Test()
  Dim i As Long
 
  For i = 0 To 15
    Sheet5.Range("AE:AE,BG:BG").Offset(, i).EntireColumn.Hidden = Range("AF35").Offset(i).Value = "NO"
  Next
End Sub

 

[srikanth sare]

Thanks for the reply
the code solved the problem but i have one more query

Here in second range i have to hide two adjacent column.

awaiting for your reply.

If Range("$AF$35").Value = "NO" Then
Sheet5.Range("AI:AI,BV:BW").EntireColumn.Hidden = True
Else
Sheet5.Range("AI:AI,BV:BW").EntireColumn.Hidden = False
End If

If Range("$AF$36").Value = "NO" Then
Sheet5.Range("AJ:AJ,BX:BY").EntireColumn.Hidden = True
Else
Sheet5.Range("AJ:AJ,BX:BY").EntireColumn.Hidden = False
End If

 

[maabadi]

Use the same Process For this also:

Sub Test()
  Dim i As Long
 
  For i = 0 To 1
    Sheet5.Range("AI:AI,BV:BW").Offset(, i).EntireColumn.Hidden = Range("AF35").Offset(i).Value = "NO"
  Next
End Sub

 

[srikanth sare]

Use the same Process For this also:

Sub Test()
  Dim i As Long

  For i = 0 To 1
    Sheet5.Range("AI:AI,BV:BW").Offset(, i).EntireColumn.Hidden = Range("AF35").Offset(i).Value = "NO"
  Next
End Sub

the code is working fine for range AI:AI and for Range BV:BW offset is hiding different column
Kindly Help me

 

[maabadi]

Try this:

Sub Test()
  Dim i As Long
   For i = 0 To 1
    Sheet5.Range("AI:AI").Offset(, i).EntireColumn.Hidden = Range("AF35").Offset(i).Value = "NO"
    Sheet5.Range("BV:BW").Offset(, i * 2).EntireColumn.Hidden = Range("AF35").Offset(i).Value = "NO"
  Next
End Sub

 

[srikanth sare]

Sub Test() Dim i As Long For i = 0 To 1 Sheet5.Range("AI:AI").Offset(, i).EntireColumn.Hidden = Range("AF35").Offset(i).Value = "NO" Sheet5.Range("BV:BW").Offset(, i * 2).EntireColumn.Hidden = Range("AF35").Offset(i).Value = "NO" Next End Sub

Thanks It worked Perfectly.

 

[maabadi]

You're Welcome & Thanks for Feedback.

 

[Peter_SSs]

.. or you could still hide/unhide columns in both sections at once through each loop.

BTW, which sheet is the AF35 and AF36 used in this code in?

Sub Test_v2()
  Dim i As Long
 
  For i = 0 To 1
    Union(Sheet5.Range("AI:AI").Offset(, i), Sheet5.Range("BV:BW").Offset(, i * 2)).EntireColumn.Hidden = Range("AF35").Offset(i).Value = "NO"
  Next
End Sub