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
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Sort by date Sort by votes
Something like this might work:

Code: 
Sub test()

Dim ws As Worksheet
Dim ws1 As Worksheet
Dim i As Integer
Dim j As Integer

Set ws = Sheets("Settings")
Set ws1 = Sheets("Salary")

i = 4
j = 7

Do Until j > 31

    If ws.Range("D" & i).Value = "r" Then
        
        ws1.Range(Cells(j, 4), Cells(j, 57)).ClearContents
        ws1.Columns(j).Hidden = True
        
    Else: ws1.Columns(j).Hidden = False
    
    End If
    
    i = i + 1
    j = j + 1

Loop

End Sub

HTH
Roger

Edit: Hold on, it's not working. I'm testing it now. One second.
 
Last edited:
Upvote 0
Rich (BB code): 
Sub test()
Dim ws As Worksheet
Dim ws1 As Worksheet
Dim i As Integer
Dim j As Integer

Set ws = Sheets(1)
Set ws1 = Sheets(2)

i = 2
j = 7

Do Until j > 31

    If ws.Range("D" & i).Value = "r" Then
                
        ws1.Range(Cells(j, 4), Cells(j, 57)).ClearContents
        ws1.Columns(j).Hidden = True
        
    Else: ws1.Columns(j).Hidden = False
    
    End If
    
    i = i + 1
    j = j + 1

Loop

End Sub

Run time error 1004 at the bolded statement. I am not sure why. Maybe someone else can come in here and fix that part. Sorry.
 
Upvote 0
This looks right, try it on a COPY of your workbook to be safe.

Your original code had the clear ommited for D3, which is taken care of by the 2 lines of code in red, if the clear should apply to D3 the same as the others then these lines should be deleted.


Rich (BB code): 
Sub HideAndClear()
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
jasonb75 said:
This looks right, try it on a COPY of your workbook to be safe.

Your original code had the clear ommited for D3, which is taken care of by the 2 lines of code in red, if the clear should apply to D3 the same as the others then these lines should be deleted.


Rich (BB code): 
Sub HideAndClear()
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
Click to expand...

Would you be able to tell me what is wrong with my code. We practically have the same loop. :)
 
Upvote 0
I just tried yours and it runs fine, only thing you have wrong is

Rich (BB code): 
ws1.Range(Cells(j, 4), Cells(j, 57)).ClearContents

Should be
Rich (BB code): 
ws1.Range(Cells(4, j), Cells(57, j)).ClearContents

But I can't see why that would cause a runtime error, only if your version of excel supports less than 57 columns, not sure what the limits are for that but I'm sure it's higher.
 
Upvote 0
If ws1 is not the active sheet then it needs to be

Code: 
ws1.Range(ws1.Cells(4, j), ws1.Cells(57, j)).ClearContents
 
Upvote 0
Well spotted VoG, my code suffers from the same error when another sheet is active.

In either case adding

Code: 
Worksheets("Salary").Select

as the first line of the code, or VoG's solution should solve the problem.

VoG said:
If ws1 is not the active sheet then it needs to be

Code: 
ws1.Range(ws1.Cells(4, j), ws1.Cells(57, j)).ClearContents
Click to expand...
 
Last edited:
Upvote 0
jasonb75 said:
I just tried yours and it runs fine, only thing you have wrong is

Rich (BB code): 
ws1.Range(Cells(j, 4), Cells(j, 57)).ClearContents

Should be
Rich (BB code): 
ws1.Range(Cells(4, j), Cells(57, j)).ClearContents

But I can't see why that would cause a runtime error, only if your version of excel supports less than 57 columns, not sure what the limits are for that but I'm sure it's higher.
Click to expand...

VoG said:
If ws1 is not the active sheet then it needs to be

Rich (BB code): 
ws1.Range(ws1.Cells(4, j), ws1.Cells(57, j)).ClearContents
Click to expand...

Thanks guys!!!

Edit: Yeah, I knew I could put a sheet(2).select in, but it just felt like there was another way.

That would have frustrated me all day!
 
Last edited:
Upvote 0
I tried all these codes on my workbook but they all produce error 1004. Tried to find out what was wrong, but no clue. Any help?
 
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
164
Ark68
A
M
  • Question
Extracting & Manipulating Data from CSV
Replies
4
Views
568
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
210
rgree05
R
I
  • Question
Copy worksheet but the new worksheet to omit some items
2
Replies
11
Views
412
ipbr21054
I
J
  • Question
3x dependent drop-downs
Replies
0
Views
165
JohnBi
J

Forum statistics

Threads
1,214,375
Messages
6,119,164
Members
448,870
Latest member
max_pedreira

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