"Simple" Hide Column Request, Based on Cell Value; please help

[DRX1234]

Hi. I've spent hours looking for a solution and am not having any luck. I'd appreciate any help/guidance.

I have 11 columns in my spreadsheet. In the first, I want to enter a number into cell A1. I'd then like to use a macro to automatically determine how many of the other 10 Columns to hide...So to clarify...

If I entered the number 1 into cell A1, I'd like to hide 10 Columns (B:K)
If I entered the number 2, I'd like 9 columns hidden; C:K
If I entered the number 3, I'd like 8 columns hidden; D:K
If I entered the number 4, I'd like 7 columns hidden; E:K
If I entered the number 5, I'd like 6 columns hidden; F:K
If I entered the number 6, I'd like 5 columns hidden; G:K
If I entered the number 7, I'd like 4 columns hidden; H:K
If I entered the number 8, I'd like 3 columns hidden; I:K
If I entered the number 9, I'd like 2 columns hidden; J:K
If I entered the number 10, I'd like 0 columns hidden

I've found solutions that look at hiding 1 Column at a time, but I cannot find one that will account for 10 conditions. I hope the above makes sense!

Thank you very much!

 

[DRX1234]

Hi Rick. I am having one small issue and not sure how to correct it. I want to send the form out for others to use with none of the fields filled-out and with Columns D-L hidden. Before I save the file and close out of Excel, I set the value of cell C6 to 1; meaning only columns A-C are visible.

When I re-open the file, a Run-time error appears. '1004': Application-defined or object-defined error. When I click on the debug window, it highlights: Columns("D").Resize(, C6.Value - 1).Hidden = False</SPAN>

The error does not appear when I save with file with a C6 value of 2-10. Any guidance would be appreciated. Thank you.

 

[Rick Rothstein]

Hi Rick. I am having one small issue and not sure how to correct it. I want to send the form out for others to use with none of the fields filled-out and with Columns D-L hidden. Before I save the file and close out of Excel, I set the value of cell C6 to 1; meaning only columns A-C are visible.

When I re-open the file, a Run-time error appears. '1004': Application-defined or object-defined error. When I click on the debug window, it highlights: Columns("D").Resize(, C6.Value - 1).Hidden = False

The error does not appear when I save with file with a C6 value of 2-10. Any guidance would be appreciated. Thank you.

Sorry, I patched the second procedure when I saw that error in my original testing and forgot that it needed to be applied to the other procedure as well. Try replacing that highlighted bit of code with this and it should work...

Columns("D").Resize(, C6 + (C6 > 1)).Hidden = False

Just so we are clear, the entire line of code that the error appears in should look like this after you patched it...

If Abs(C6.Value - 5) < 5 Then Columns("D").Resize(, C6 + (C6 > 1)).Hidden = False

 

[DRX1234]

Perfect, thank you!! The patch removed the error message. I did note one other change and I almost hate to ask another question since you've done so much already. But when I save and close the file with Columns D-L hidden, one of the Columns doesn't stay hidden upon re-opening (Column D). How can I modify the code to correct this? Thank you again.

 

[Rick Rothstein]

Perfect, thank you!! The patch removed the error message. I did note one other change and I almost hate to ask another question since you've done so much already. But when I save and close the file with Columns D-L hidden, one of the Columns doesn't stay hidden upon re-opening (Column D). How can I modify the code to correct this? Thank you again.

Sorry again... in looking at the code more closely, I see I had changed more on that code line than I had remembered. Just so anyone coming back to this thread in the future doesn't have to try and piece things together from multiple messages, let me post both procedures again with all correction in place...

'  Put this code in the ThisWorkbook code module
  '  (double click ThisWorkbook in the <acronym title="visual basic for applications">VBA</acronym> Project window)
Private Sub Workbook_Open()
  Dim C6 As Range
  With Worksheets("Sheet1")
    Set C6 = .Range("C6")
    .Columns("D").Resize(, Columns.Count - 3).Hidden = True
    C6.Select
    If IsNumeric(C6.Value) And Len(C6.Value) > 0 Then
      If C6 > 1 And C6 < 10 Then Columns("D").Resize(, C6 + (C6 > 1)).Hidden = False
    End If
  End With
End Sub

'  Put this in the worksheet module for whatever worksheet is to
'  have this functionality (see the red highlighted text above)
Private Sub Worksheet_Change(ByVal Target As Range)
  Dim C6 As Variant
  C6 = Range("C6").Value
  If IsNumeric(C6) And Len(C6) > 0 Then
    Columns("D:L").Hidden = True
    If C6 > 1 And C6 < 10 Then Columns("D").Resize(, C6 + (C6 > 1)).Hidden = False
  End If
End Sub

 

[DRX1234]

AWESOME!!!!! Thank you sooooooooo much. Everything runs perfectly and looks really sharp. Thanks again for your help. Enjoy the weekend!!

 

[DRX1234]

The above solution worked great, however, there was a screen re-sizing issue when a user was inputting data into columns that were to the far-right; it made the spreadsheet pretty unusable, as you would have to re-find your place after entering data into any cell. In case anyone cares, here is a different solution that I've found to work for the above scenario....

Private Sub worksheet_change(ByVal target As Range)
If Not Intersect(Range("C6"), target) Is Nothing Then
Columns(4).Resize(, Columns.Count - 4).Hidden = True
Columns(3).Resize(, Range("C6") + 0).Hidden = False
End If
End Sub

 

[Rick Rothstein]

The above solution worked great, however, there was a screen re-sizing issue when a user was inputting data into columns that were to the far-right; it made the spreadsheet pretty unusable, as you would have to re-find your place after entering data into any cell. In case anyone cares, here is a different solution that I've found to work for the above scenario....

Private Sub worksheet_change(ByVal target As Range)
If Not Intersect(Range("C6"), target) Is Nothing Then
Columns(4).Resize(, Columns.Count - 4).Hidden = True
Columns(3).Resize(, Range("C6") + 0).Hidden = False
End If
End Sub

Sorry, that was sloppy coding on my part. Untested, but I think if you wrap the code I posted with...

If Target.Address(0,0) = "C6" Then
.....
End If

..then I think my code would have worked problem-free

 

[Rick Rothstein]

Sorry, that was sloppy coding on my part. Untested, but I think if you wrap the code I posted with...

If Target.Address(0,0) = "C6" Then
.....
End If

..then I think my code would have worked problem-free

(Still untested) Actually, sticking with the code as I originally posted it and changing this line of code..

If IsNumeric(C6.Value) And Len(C6.Value) > 0 Then

to this should avoid the problem you mentioned...

If IsNumeric(C6.Value) And Len(C6.Value) > 0 [B][COLOR=#ff0000]And Target.Address(0, 0) = "C6" [/COLOR][/B]Then

 

[DRX1234]

Thanks again Rick! I appreciate your time!!