Vba looping thru cells and unhiding column based on value

[luca142]

Hi,

This is my first post and I'm a vba rookie, so please be gentle.
What I'm trying to do:
If any of the cells in range B3:B45 contains a one specific type of document (" customised"), which user can choose from droplist , the column C is unhiding. If " customised " wasn't chosen, the column stays hidden.

I' ve tried the following:

Sub ifloop
Dim rcell As Range, rng As Range
Set ring = Thisworkbook.Worksheets("Sheet1").Range("B3:B45")
For Each rcell In rng.Cells
If rcell.Value="customised" Then
Columns("C").Entirecolumn.Hidden= False
End if
Next rcell
End sub

But firstly it works only when I press Run sub, and secondly it should hide column C if answer was changed from customised to standard, resulting in no " customised" answer in cell range

Help

 

[Joe4]

Welcome to the Board!

You have a few issues in your code. Firstly, you have a typo here:

Set ring = Thisworkbook.Worksheets("Sheet1").Range("B3:B45")

Secondly, you are telling when to unhide, but there are no commands in it telling it when to hide.

But firstly it works only when I press Run sub

So, are you looking to have this run automatically?
If so, exactly when should the code run?
Only when there is a manual change made to the range B3:B45, or something else?

 

[LazyBug]

Hello and welcome to MrExcel!
As far as I could understand, by default the column C should always be hidden, except if it matches a criterion from the specified range.
If so, then to the sheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
    Columns(3).Hidden = True
    If Not Intersect(Target, Range("B3:B45")) Is Nothing Then
        If Trim(Target.Value) = "customised" Then Columns(3).Hidden = False
    End If
End Sub

 

[luca142]

Welcome to the Board!

You have a few issues in your code. Firstly, you have a typo here:

Set ring = Thisworkbook.Worksheets("Sheet1").Range("B3:B45")

Secondly, you are telling when to unhide, but there are no commands in it telling it when to hide.


So, are you looking to have this run automatically?
If so, exactly when should the code run?
Only when there is a manual change made to the range B3:B45, or something else?

Joe4 thank you, Indeed I made a typo ( Facepalm).

Regarding hide:
I made
Else
Columns("C").Entirecolumn.Hidden= True

But it didn't worked.

Yes, I wanted macro to run automatically, just like you described: manual change in B cells

 

[Joe4]

Joe4 thank you, Indeed I made a typo ( Facepalm).

Regarding hide:
I made
Else
Columns("C").Entirecolumn.Hidden= True

But it didn't worked.

Yes, I wanted macro to run automatically, just like you described: manual change in B cells

Do you want it to just look at the value in column B that was just updated, or to look at every entry in column B every time, and unhide the whole column if ANY cell is equal to "customised"?

 

[luca142]

Do you want it to just look at the value in column B that was just updated, or to look at every entry in column B every time, and unhide the whole column if ANY cell is equal to "customised"?

The column should be unhidden if Any cell is equal to customised because it contains additional question for this type.

 

[Joe4]

Go to Sheet 1, right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste this VBA code in the VB Editor window that pops up:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rcell As Range, rng As Range

    Set rng = Range("B3:B45")
    If Intersect(Target, rng) Is Nothing Then Exit Sub
        
    Columns("C").EntireColumn.Hidden = True
    For Each rcell In rng.Cells
        If rcell.Value = "customised" Then
            Columns("C").EntireColumn.Hidden = False
            Exit Sub
        End If
    Next rcell
    
End Sub

This should do what you want automatically as you manually update column B.

 

[luca142]

Go to Sheet 1, right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste this VBA code in the VB Editor window that pops up:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rcell As Range, rng As Range

    Set rng = Range("B3:B45")
    If Intersect(Target, rng) Is Nothing Then Exit Sub
       
    Columns("C").EntireColumn.Hidden = True
    For Each rcell In rng.Cells
        If rcell.Value = "customised" Then
            Columns("C").EntireColumn.Hidden = False
            Exit Sub
        End If
    Next rcell
   
End Sub

This should do what you want automatically as you manually update column B.

Joe4, Thank you for your help and patience It's working!

 

[Joe4]

You are welcome.
Glad we were able to help!