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

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
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

R
  • Question
Copy and unfilter in VBA
Replies
1
Views
157
Alex Blakenburg
A
E
  • Solved
Quick VBA code to Hide Rows based on Cell Value
Replies
3
Views
195
Joe4
Joe4
N
  • Question
Hiding/showing rows between two values
Replies
1
Views
310
Joe4
Joe4
I
  • Solved
Advice for my code that hides shapes on worksheet
Replies
3
Views
109
dmt32
D
I
  • Question
Advice for slight alteration to existing working code
Replies
1
Views
167
ipbr21054
I

Forum statistics

Threads
1,221,521
Messages
6,160,302
Members
451,637
Latest member
hvp2262

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