How to Hide 2 Columns if a column has a value?-CodePudding

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Range("M1:N1").Columns(1).Value = "ΕΜΒΑΣΜΑ" Then
        Columns("U").EntireColumn.Hidden = False
        Columns("V").EntireColumn.Hidden = False
    Else
        Columns("U").EntireColumn.Hidden = True
        Columns("V").EntireColumn.Hidden = True
    End If
End Sub

So I have been having trouble with this code here. What I want to do is hide U, V columns if there is a value in M column called "ΕΜΒΑΣΜΑ".

Every time I let it run, it automatically hides the columns even if I have the value already in my column. Other than that, it doesn't seem to work in real time so even if I change anything, nothing happens.

Any ideas?

CodePudding user response：

(a) If you want to check a whole column, you need to specify the whole column, e.g. with Range("M:M").
(b) You can't compare a Range that contains more than one cell with a value. If Range("M:M").Columns(1).Value = "ΕΜΒΑΣΜΑ" Then will throw a Type mismatch error (13). That is because a Range containing more that cell will be converted into a 2-dimensional array and you can't compare an array with a single value.

One way to check if a column contains a specific value is with the CountIf-function:

If WorksheetFunction.CountIf(Range("M:M"), "ΕΜΒΑΣΜΑ") > 0 Then

To shorten your code, you could use

Dim hideColumns As Boolean
hideColumns = (WorksheetFunction.CountIf(Range("M:M"), "ΕΜΒΑΣΜΑ") = 0)
Columns("U:V").EntireColumn.Hidden = hideColumns

Update If you want to use that code in other events than a worksheet event, you should specify on which worksheet you want to work. Put the following routine in a regular module:

Sub showHideColumns(ws as Worksheet)
    Dim hideColumns As Boolean
    hideColumns = (WorksheetFunction.CountIf(ws.Range("M:M"), "ΕΜΒΑΣΜΑ") = 0)
    ws.Columns("U:V").EntireColumn.Hidden = hideColumns
End Sub

Now all you have to do is to call that routine whenever you want and pass the worksheet as parameter. This could be the Workbook.Open - Event, or the click event of a button or shape. Eg put the following code in the Workbook module:

Private Sub Workbook_Open()
    showHideColumns ThisWorkbook.Sheets(1)
End Sub

CodePudding user response：

on a fast hand I would go like this... maybe someone can do it shorter...

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)


Dim sht As Worksheet: Set sht = ActiveSheet
Dim c As Range

With sht.Range("M1:M" & sht.Cells(sht.Rows.Count, "M").End(xlUp).Row)
    Set c = .Find("XXX", LookIn:=xlValues)
    If Not c Is Nothing Then
        Columns("U:V").EntireColumn.Hidden = True
    Else
        Columns("U:V").EntireColumn.Hidden = False
    End If
End With

End Sub