Hiding columns

[Colin Mannion]

Hi

Hoping you can help.

I have a sheet where there are a number of columns which I need to hide IF there is no relevant information to display - see attached sample file.

The condition is: where the entry for a given retailer in row 9 is "No promotion", then hide that column.

Sure there must be some code I can run that will be able to do this....can you help?

Thanks in advance.
Colin

 

[PCosta87]

Hi

Hoping you can help.

I have a sheet where there are a number of columns which I need to hide IF there is no relevant information to display - see attached sample file.

The condition is: where the entry for a given retailer in row 9 is "No promotion", then hide that column.

Sure there must be some code I can run that will be able to do this....can you help?

Thanks in advance.
Colin

Hi,

This should do it for the available range "C9:N9"... change as necessary:

Sub hidecolumns()

    Dim c As Range
 
    For Each c In Range("C9:N9")
        If c = "No Promotion" Then
            c.EntireColumn.Hidden = True
        End If
    Next c

End Sub

Hope this helps

 

[jindon]

Like this?

Sub test()
    Dim r As Range
    For Each r In Rows(9).SpecialCells(2, 2)
        r.EntireColumn.Hidden = r.Value = "No Promotion"
    Next
End Sub

 

[Marc L]

Hi !

Or this way very closed to jindon's code :​

Sub Demo()
     Dim Rg As Range
     Application.ScreenUpdating = False
For Each Rg In Range("C9", Cells(9, Columns.Count).End(xlToRight))
         Rg.EntireColumn.Hidden = Rg.Value = "No Promotion"
Next
     Application.ScreenUpdating = True
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[Colin Mannion]

Thank you everyone - all so helpful....all sorted!
Colin

 

[Colin Mannion]

Hi All

Thanks for helping sort the previous problem I had - it's working a treat!

There is another one which is similar to that which I would appreciate your help on...

The file attached shows that there are a number of rows where every entry for the cell is "No Promotion". I need to be able to hide these and just leave ones which have actually have a Promo in them if that makes sense.

Any ideas?

Thanks in advance.
Colin

 

[jindon]

Messy data range...
1) Clear col.A or even delete col.A

Sub test()
    Dim i As Long
    With [b3].CurrentRegion
        For i = 2 To .Rows.Count
            .Rows(i).EntireRow.Hidden = Application.CountIf(.Rows(i).Offset(, 1), "No Promotion") = .Columns.Count - 1
        Next
    End With
End Sub

 

[Colin Mannion]

Hiya

Thanks for this - Works a treat

Apologies for the oversight, but I will then need to unhide the rows - can you also help with that?

Thanks again
Colin

 

[jindon]

Is that

Sub unhide()
    Rows.Hidden = False
End Sub

?

 

[Colin Mannion]

as simple as that!!

Thanks again for the help jindon.
Colin