"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!

 

[Mr.Daines]

Hello,

I have started the code for you.

All you need to do is right click on the sheet tab, view code, then paste the code below.

Follow my examples, 'Case 1' and 'Case 2' and type the rest for the remaining 3 to 10 cases.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Target.Worksheet.Range("A1")) Is Nothing Then Call Hide_Column_Request
End Sub

Sub Hide_Column_Request()
Dim HideNumber As Integer
HideNumber = Range("A1").Value
Columns("B:K").Select
Selection.EntireColumn.Hidden = False
Application.ScreenUpdating = False
    Select Case HideNumber
        'The number that follows Case is correspondant to the number entered into "A1"
    
        Case 1
                Columns("B:K").Select
                Selection.EntireColumn.Hidden = True
        
        Case 2
                Columns("C:K").Select
                Selection.EntireColumn.Hidden = True

End Select
Application.ScreenUpdating = True
End Sub

I'm not the best at VBA and there is likely to be an easier or more efficient example available, however, this works just fine.

Hope that helps.

 

[Rick Rothstein]

Give this macro a try...

Sub HideColumnsBasedOnNumberInCellA1()
  Dim A1 As Variant
  A1 = Range("A1").Value
  If IsNumeric(A1) Then
    Columns("B:K").Hidden = False
    If Abs(A1 - 5) < 5 Then Columns("K").Offset(, A1 - 10).Resize(, 11 - A1).Hidden = True
  End If
End Sub

 

[DRX1234]

Hello Mr. Daines. Thank you for taking the time to respond to my post. I did run into an issue when I tried the above, all columns except for Column A hide. Maybe I am typing in something wrong; or maybe since the cell I am actually inputting the value into is actually in Column C. Would you mind giving my code a quick look-over?

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Target.Worksheet.Range("C6")) Is Nothing Then Call Hide_Column_Request
End Sub
Sub Hide_Column_Request()
Dim HideNumber As Integer
HideNumber = Range("C6").Value
Columns("D:L").Select
Selection.EntireColumn.Hidden = False
Application.ScreenUpdating = False
Select Case HideNumber
'The number that follows Case is correspondant to the number entered into "C6"

Case 1
Columns("D:L").Select
Selection.EntireColumn.Hidden = True

Case 2
Columns("E:L").Select
Selection.EntireColumn.Hidden = True
End Select
Application.ScreenUpdating = True
End Sub

Thank you!!

 

[DRX1234]

Hello Rick,

Thank you for looking over my post. I gave your code a shot but no luck. I right clicked the sheet tab, selected view code and pasted your code into the VBA window. I did make some adjustment to cells and column ranges, but is there anything else I need to do? Here is what I have:

Sub HideColumnsBasedOnNumberInCellC6()
Dim C6 As Variant
C8 = Range("C6").Value
If IsNumeric(C6) Then
Columns("D:L").Hidden = False
If Abs(C6 - 5) < 5 Then Columns("L").Offset(, C6 - 10).Resize(, 10 - C6).Hidden = True
End If
End Sub

Thank you!!

 

[brian.wethington]

Hello Rick,

Thank you for looking over my post. I gave your code a shot but no luck. I right clicked the sheet tab, selected view code and pasted your code into the VBA window. I did make some adjustment to cells and column ranges, but is there anything else I need to do? Here is what I have:

Sub HideColumnsBasedOnNumberInCellC6()
Dim C6 As Variant
C8 = Range("C6").Value
If IsNumeric(C6) Then
Columns("D:L").Hidden = False
If Abs(C6 - 5) < 5 Then Columns("L").Offset(, C6 - 10).Resize(, 10 - C6).Hidden = True
End If
End Sub

Thank you!!

You changed your variable to C6 it appears, but then you initialize C8 instead of C6. Then you continue working with C6 which has no value.

 

[Rick Rothstein]

....or maybe since the cell I am actually inputting the value into is actually in Column C.

Columns("D:L").Select

I can never understand why people who ask questions here think they have to "hide" their actual set up from us and tell us "fake" data and/or layout. What you are now saying you have does no equate to what you told us originally. Your original span of columns was B:K (10 columns with anywhere between 2 and 10 of them remaining visible), but now your range of columns is D:L which is only 9 columns! So, does that mean if the entered number is 9, only one of the columns (Column L maybe?) will be hidden instead of the two (J:K) your original post wanted?

Hello Rick,

Thank you for looking over my post. I gave your code a shot but no luck. I right clicked the sheet tab, selected view code and pasted your code into the VBA window. I did make some adjustment to cells and column ranges, but is there anything else I need to do? Here is what I have:

Sub HideColumnsBasedOnNumberInCellC6()
Dim C6 As Variant
C8 = Range("C6").Value
If IsNumeric(C6) Then
Columns("D:L").Hidden = False
If Abs(C6 - 5) < 5 Then Columns("L").Offset(, C6 - 10).Resize(, 10 - C6).Hidden = True
End If
End Sub

Thank you!!

The code was designed for what you told us originally. Once you tell us exactly what you have and what you want to do with it (like you did for your "fake" data in your original message), I will be happy to modify it for you (and I'll convert it to Change event code as well).

 

[DRX1234]

Sorry for the confusion Rick. I wasn't trying to hide what I was doing, I just wanted to provide a stripped down overview. So, I'm designing a sample analysis request form. In Cell C6, I've asked the submitter to input the number of samples they plan to hand-over for analysis. Within the same Column, down a little further is where they can input information for their first sample. Information for their second sample can be input into Column D and it goes on like that until Column L (10 samples max - Columns C through L). Incidentally, Columns A and B are just for lab info/data, etc.

My vision, is to have the submitter open the file where only Columns A-C will be visible. Meaning, they can input data for just 1 sample. However, if they want to submit more than 1 sample, they can type, for example, the number 8 into cell C6, and the speadsheet would automatically unhide Columns D-J (samples 1-8). I hope I'm explaining correctly.

Thanks again for taking the time to read this and for helping me clarify exactly what I am doing for the rest of the board.

 

[Rick Rothstein]

I just wanted to provide a stripped down overview.

My suggestion for future questions you may ask online is to not do that.

My vision, is to have the submitter open the file where only Columns A-C will be visible. Meaning, they can input data for just 1 sample. However, if they want to submit more than 1 sample, they can type, for example, the number 8 into cell C6, and the speadsheet would automatically unhide Columns D-J (samples 1-8). I hope I'm explaining correctly.

The code to do what you envision above is structurely different than the code to do what you asked for initially. Put the following code procedures in the indicated modules and I think it will work as you want. One thing, though, since you did not tell us the name of the worksheet needing this functionality, I guessed at Sheet1... if that is wrong, then change the red highlighted text to the correct name.

'  Put this code in the ThisWorkbook code module 
'  (double click ThisWorkbook in the VBA 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 Abs(C6.Value - 5) < 5 Then Columns("D").Resize(, C6.Value - 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]

Wow Rick - worked like a charm. Thank you so much. And point taken, I'll be as detailed as possible going forward. I am really impressed, thank you again!!