Hide Columns Based on Other Sheet Cell Value

[VrRotate]

Hello,

I've used several examples and can't get this to work.

Condition:
Drop down on Sheet1 cell B7 Yes or No

If No hide Sheet2 Columns CN - EL
If Yes show Sheet2 Columns CN - EL

Want to work in real time as user selects Yes or No, if that's possible?

Thank you in advance for all your help!

 

[Fluff]

So you mean 1,048,576 Ns in each column?

 

[VrRotate]

So you mean 1,048,576 Ns in each column?

Well, hopefully not, hopefully only in rows that had data in other columns.....tricky

 

[Fluff]

Is there any column that will always have data on the last used row?

 

[mumps]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Target, Range("B7")) Is Nothing Then Exit Sub
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Select Case Target.Value
        Case "Yes"
            Sheets("Sheet2").Columns("CN:EL").Hidden = False
            Sheets("Sheet2").Range("CN2:EL" & LastRow).ClearContents
        Case "No"
            Sheets("Sheet2").Range("CN2:EL" & LastRow) = "N"
            Sheets("Sheet2").Columns("CN:EL").Hidden = True
    End Select
End Sub

 

[VrRotate]

Amazing thank you!

 

[VrRotate]

So I just realized this is working well except its stopping the "N" after row 18...any ideas?

 

[VrRotate]

Is there any column that will always have data on the last used row?

Yes, the UPC.

So I just realized this is working well except its stopping the "N" after row 18...any ideas?