simplify the VBA code

srikanth sare

New Member
Joined
May 1, 2020
Messages
30
Office Version
  1. 2013
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web
VBA Code:
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
 
Last edited by a moderator:

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
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

VBA Code:
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
 
Upvote 0
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
 
Upvote 0
Use the same Process For this also:
VBA Code:
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
 
Upvote 0
Use the same Process For this also:
VBA Code:
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
 
Upvote 0
Try this:
VBA Code:
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
 
Upvote 0
Solution
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.
 
Upvote 0
.. 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?

VBA Code:
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
 
Upvote 0

Forum statistics

Threads
1,223,247
Messages
6,171,007
Members
452,374
Latest member
keccles

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
Back
Top