Simplifying code to hide and clear contents

D

ditto

New Member
Joined
Mar 21, 2009
Messages
20
I use the following code to hide and clear contents in cells based on cell values in another worksheet. Can I simplify this code?
Code: 
Sub HideAndClear()

  If Worksheets("Settings").Range("D2").Value = "r" Then
     Worksheets("Salary").Range("G4:G57").ClearContents
     Worksheets("Salary").Columns("G").Hidden = True
  Else: Worksheets("Salary").Columns("G").Hidden = False
  End If
  If Worksheets("Settings").Range("D3").Value = "r" Then
     Worksheets("Salary").Columns("H").Hidden = True
  Else: Worksheets("Salary").Columns("H").Hidden = False
  End If
  If Worksheets("Settings").Range("D4").Value = "r" Then
     Worksheets("Salary").Range("I4:I57").ClearContents
     Worksheets("Salary").Columns("I").Hidden = True
  Else: Worksheets("Salary").Columns("I").Hidden = False
  End If
  If Worksheets("Settings").Range("D5").Value = "r" Then
     Worksheets("Salary").Range("J4:J57").ClearContents
     Worksheets("Salary").Columns("J").Hidden = True
  Else: Worksheets("Salary").Columns("J").Hidden = False
  End If
  If Worksheets("Settings").Range("D6").Value = "r" Then
     Worksheets("Salary").Range("K4:K57").ClearContents
     Worksheets("Salary").Columns("K").Hidden = True
  Else: Worksheets("Salary").Columns("K").Hidden = False
  End If
  If Worksheets("Settings").Range("D7").Value = "r" Then
     Worksheets("Salary").Range("L4:L57").ClearContents
     Worksheets("Salary").Columns("L").Hidden = True
  Else: Worksheets("Salary").Columns("L").Hidden = False
  End If
  If Worksheets("Settings").Range("D8").Value = "r" Then
     Worksheets("Salary").Range("M4:M57").ClearContents
     Worksheets("Salary").Columns("M").Hidden = True
  Else: Worksheets("Salary").Columns("M").Hidden = False
  End If
  If Worksheets("Settings").Range("D9").Value = "r" Then
     Worksheets("Salary").Range("N4:N57").ClearContents
     Worksheets("Salary").Columns("N").Hidden = True
  Else: Worksheets("Salary").Columns("N").Hidden = False
  End If
  If Worksheets("Settings").Range("D10").Value = "r" Then
     Worksheets("Salary").Range("O4:O57").ClearContents
     Worksheets("Salary").Columns("O").Hidden = True
  Else: Worksheets("Salary").Columns("O").Hidden = False
  End If
  If Worksheets("Settings").Range("D11").Value = "r" Then
     Worksheets("Salary").Range("Q4:Q57").ClearContents
     Worksheets("Salary").Columns("Q").Hidden = True
  Else: Worksheets("Salary").Columns("Q").Hidden = False
  End If
  If Worksheets("Settings").Range("D12").Value = "r" Then
     Worksheets("Salary").Range("R4:R57").ClearContents
     Worksheets("Salary").Columns("R").Hidden = True
  Else: Worksheets("Salary").Columns("R").Hidden = False
  End If
  If Worksheets("Settings").Range("D13").Value = "r" Then
     Worksheets("Salary").Range("S4:S57").ClearContents
     Worksheets("Salary").Columns("S").Hidden = True
  Else: Worksheets("Salary").Columns("S").Hidden = False
  End If
  If Worksheets("Settings").Range("D14").Value = "r" Then
     Worksheets("Salary").Range("T4:T57").ClearContents
     Worksheets("Salary").Columns("T").Hidden = True
  Else: Worksheets("Salary").Columns("T").Hidden = False
  End If
  If Worksheets("Settings").Range("D15").Value = "r" Then
     Worksheets("Salary").Range("U4:U57").ClearContents
     Worksheets("Salary").Columns("U").Hidden = True
  Else: Worksheets("Salary").Columns("U").Hidden = False
  End If
  If Worksheets("Settings").Range("D16").Value = "r" Then
     Worksheets("Salary").Range("V4:V57").ClearContents
     Worksheets("Salary").Columns("V").Hidden = True
  Else: Worksheets("Salary").Columns("V").Hidden = False
  End If
  If Worksheets("Settings").Range("D17").Value = "r" Then
     Worksheets("Salary").Range("W4:W57").ClearContents
     Worksheets("Salary").Columns("W").Hidden = True
  Else: Worksheets("Salary").Columns("W").Hidden = False
  End If
  If Worksheets("Settings").Range("D18").Value = "r" Then
     Worksheets("Salary").Range("X4:X57").ClearContents
     Worksheets("Salary").Columns("X").Hidden = True
  Else: Worksheets("Salary").Columns("X").Hidden = False
  End If
  If Worksheets("Settings").Range("D19").Value = "r" Then
     Worksheets("Salary").Range("Y4:Y57").ClearContents
     Worksheets("Salary").Columns("Y").Hidden = True
  Else: Worksheets("Salary").Columns("Y").Hidden = False
  End If
  If Worksheets("Settings").Range("D20").Value = "r" Then
     Worksheets("Salary").Range("Z4:Z57").ClearContents
     Worksheets("Salary").Columns("Z").Hidden = True
  Else: Worksheets("Salary").Columns("Z").Hidden = False
  End If
  If Worksheets("Settings").Range("D21").Value = "r" Then
     Worksheets("Salary").Range("AA4:AA57").ClearContents
     Worksheets("Salary").Columns("AA").Hidden = True
  Else: Worksheets("Salary").Columns("AA").Hidden = False
  End If
  If Worksheets("Settings").Range("D22").Value = "r" Then
     Worksheets("Salary").Range("AB4:AB57").ClearContents
     Worksheets("Salary").Columns("AB").Hidden = True
  Else: Worksheets("Salary").Columns("AB").Hidden = False
  End If
  If Worksheets("Settings").Range("D23").Value = "r" Then
     Worksheets("Salary").Range("AC4:AC57").ClearContents
     Worksheets("Salary").Columns("AC").Hidden = True
  Else: Worksheets("Salary").Columns("AC").Hidden = False
  End If
  If Worksheets("Settings").Range("D24").Value = "r" Then
     Worksheets("Salary").Range("AD4:AD57").ClearContents
     Worksheets("Salary").Columns("AD").Hidden = True
  Else: Worksheets("Salary").Columns("AD").Hidden = False
  End If
  If Worksheets("Settings").Range("D25").Value = "r" Then
     Worksheets("Salary").Range("AE4:AE57").ClearContents
     Worksheets("Salary").Columns("AE").Hidden = True
  Else: Worksheets("Salary").Columns("AE").Hidden = False
  End If
  If Worksheets("Settings").Range("D26").Value = "r" Then
     Worksheets("Salary").Range("AF4:AF57").ClearContents
     Worksheets("Salary").Columns("AF").Hidden = True
  Else: Worksheets("Salary").Columns("AF").Hidden = False
  End If
  If Worksheets("Settings").Range("D27").Value = "r" Then
     Worksheets("Salary").Range("AG4:AG57").ClearContents
     Worksheets("Salary").Columns("AG").Hidden = True
  Else: Worksheets("Salary").Columns("AG").Hidden = False
  End If
  If Worksheets("Settings").Range("D28").Value = "r" Then
     Worksheets("Salary").Range("AH4:AH57").ClearContents
     Worksheets("Salary").Columns("AH").Hidden = True
  Else: Worksheets("Salary").Columns("AH").Hidden = False
  End If
  If Worksheets("Settings").Range("D29").Value = "r" Then
     Worksheets("Salary").Range("AI4:AI57").ClearContents
     Worksheets("Salary").Columns("AI").Hidden = True
  Else: Worksheets("Salary").Columns("AI").Hidden = False
  End If
  If Worksheets("Settings").Range("D30").Value = "r" Then
     Worksheets("Salary").Range("AJ4:AJ57").ClearContents
     Worksheets("Salary").Columns("AJ").Hidden = True
  Else: Worksheets("Salary").Columns("AJ").Hidden = False
  End If
  If Worksheets("Settings").Range("D31").Value = "r" Then
     Worksheets("Salary").Range("AK4:AK57").ClearContents
     Worksheets("Salary").Columns("AK").Hidden = True
  Else: Worksheets("Salary").Columns("AK").Hidden = False
  End If
  If Worksheets("Settings").Range("D32").Value = "r" Then
     Worksheets("Salary").Range("AL4:AL57").ClearContents
     Worksheets("Salary").Columns("AL").Hidden = True
  Else: Worksheets("Salary").Columns("AL").Hidden = False
  End If
  If Worksheets("Settings").Range("D33").Value = "r" Then
     Worksheets("Salary").Range("AM4:AM57").ClearContents
     Worksheets("Salary").Columns("AM").Hidden = True
  Else: Worksheets("Salary").Columns("AM").Hidden = False
  End If

End Sub
 
Sort by date Sort by votes
Hi Ditto

THe discussion between rconverse, VoG and myself was relating to this problem, which I've just had with some other code as well. Hopefully this one should resolve the problem, if not can you select debug when the error occurs and post the line of code with the yellow background.

Rich (BB code): 
Sub HideAndClear()
Worksheets("Salary").Select
srow = 2
 scol = 7
 While srow <= 33
   If Worksheets("Settings").Range("D" & srow).Value = "r" Then
    If srow <> 3 Then
     Worksheets("Salary").Range(Cells(4, scol), Cells(57, scol)).ClearContents
   End If
     Worksheets("Salary").Columns(scol).Hidden = True
  Else: Worksheets("Salary").Columns(scol).Hidden = False
  End If
 srow = srow + 1
 scol = scol + 1
 Wend
End Sub

Hope this helps

Jason
 
Upvote 0

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Now the code works perfectly well. But the problem is that each time the value in column D of worksheet "Settings" changes worksheet "Salary" becomes the active sheet. So I have to move back to "Settings" to change another value. The active sheet should remain as "Settings". How do I achieve this?
 
Upvote 0
Try this version, see if it's ok, might pause briefly while it runs.

Code: 
Sub HideAndClear()
Application.ScreenUpdating = False
a = ActiveSheet.Name
Worksheets("Salary").Select
srow = 2
 scol = 7
 While srow <= 33
   If Worksheets("Settings").Range("D" & srow).Value = "r" Then
    If srow <> 3 Then
     Worksheets("Salary").Range(Cells(4, scol), Cells(57, scol)).ClearContents
   End If
     Worksheets("Salary").Columns(scol).Hidden = True
  Else: Worksheets("Salary").Columns(scol).Hidden = False
  End If
 srow = srow + 1
 scol = scol + 1
 Wend
Sheets(a).Select
Application.ScreenUpdating = True
End Sub

Jason
 
Upvote 0
In my original code clear contents was not set for column D and P of "Salary". They were exempted. Is it possible to add the excemption in some way?
 
Upvote 0
I think this is right for that, I did include it before, but it had column C instead of D, also missed column P when I looked over your original.

Code: 
Sub HideAndClear()
Application.ScreenUpdating = False
a = ActiveSheet.Name
Worksheets("Salary").Select
srow = 2
 scol = 7
 While srow <= 33
   If Worksheets("Settings").Range("D" & srow).Value = "r" Then
    If srow <> 4 And srow <> 16 Then
     Worksheets("Salary").Range(Cells(4, scol), Cells(57, scol)).ClearContents
   End If
     Worksheets("Salary").Columns(scol).Hidden = True
  Else: Worksheets("Salary").Columns(scol).Hidden = False
  End If
 srow = srow + 1
 scol = scol + 1
 Wend
Sheets(a).Select
Application.ScreenUpdating = True
End Sub

JB
 
Upvote 0
Thanks a lot Jason, that was quite enlightening. I am rather new to vba and hope to get your help in future too. Thanks a lot to rconverse and VoG too. You are all such wonderful people there at mrexcel.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
After User Enters A Value (Validation List), Not Automatically Advancing To Next Cell As Per "Select"
Replies
1
Views
176
Ark68
A
M
  • Question
Extracting & Manipulating Data from CSV
Replies
4
Views
586
rlv01
R
R
  • Question
How to combine the functions of having the ability to select multiple items from a drop down list and hide columns based on values of said list?
Replies
0
Views
216
rgree05
R
I
  • Question
Copy worksheet but the new worksheet to omit some items
2
Replies
11
Views
432
ipbr21054
I
J
  • Question
3x dependent drop-downs
Replies
0
Views
183
JohnBi
J

Forum statistics

Threads
1,214,911
Messages
6,122,198
Members
449,072
Latest member
DW Draft

Share this page

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