Combining IF with LEFT to hide columns VBA

[danno79]

Hi Guys
I'm trying to write some code to Hide columns if the first 3 characters of cells in a range equal the contents of another (formula equivalent would be something like this =IF((LEFT(A2,3))=A1,"HIDE COLUMN", "SHOW COLUMN"). I have gotten this far but cant get it to work;-



Private Sub Worksheet_Change(ByVal Target As Range)

Dim r As Range, cell As Range

On Error GoTo ErrHandler
Set r = Me.Range("B7:CG7")

Application.ScreenUpdating = False
Application.EnableEvents = False

Row = 1
col = 1

For Each cell In r
If cell.Value = "" And Left(cell.Value, 3) = cell(Row, col).Value Then
cell.EntireColumn.Hidden = True
Else
cell.EntireColumn.Hidden = False

End If


Next
ErrHandler:
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub


Think the problem is in the if statement - any help greatly appreciated

Cheers

 

[VoG]

Hello and welcome to MrExcel.

Shouldn't it be?

If cell.Value <> "" And Left(cell.Value, 3) = cell(Row, col).Value Then

 

[Norie]

Well there could be a problem with the logic of the If statement.

How can the value of the cell equal "" and the first 3 characters of the cell match the value in A1?

Perhaps you mean <>"" rather than =""?

And shouldn't it be Cells(Row, col).Value?

 

[danno79]

thanks - have tried both these suggestions but still can't get it to work, I started out with a working peice of code for hiding columns if the value of cells in range was blank i.e If cell.Value = "". Could there be a more fundamental problem with what I'm tring to do with the "left part" (in case you hadn't already guessed I'm a total newb to VBA )

 

[VoG]

Where did you put the code? You need to right click the sheet tab, select View Code and paste in the code.

 

[danno79]

Yes - pretty sure code code is in the right place as it is for a worksheet change - plus already had similar code working :-

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim r As Range, cell As Range
    On Error GoTo ErrHandler
    Set r = Me.Range("C8:R8")
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    For Each cell In r
        If cell.Value = "" Then
        cell.EntireColumn.Hidden = True
        Else
        cell.EntireColumn.Hidden = False
   
        End If
 
        Next

ErrHandler:
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub

Any other ideas?

 

[Norie]

Remove On Error..., that could just be hiding errors.

Check events are enabled by opening the immediate window (CTRL+G), typing this and then enter.

? Application.EnableEvents

Should return True if events are enabled.

 

[VoG]

This works for me

Private Sub Worksheet_Change(ByVal Target As Range)

Dim r As Range, cell As Range
Dim iRow As Long, iCol As Integer

Set r = Me.Range("B7:CG7")
Application.ScreenUpdating = False
Application.EnableEvents = False

iRow = 1
iCol = 1

For Each cell In r
    cell.EntireColumn.Hidden = cell.Value <> "" And Left(cell.Value, 3) = Cells(iRow, iCol).Value
Next cell
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

 

[danno79]

Thanks VoG perfect!

 

[danno79]

well maybr=e not so perfect - works the reverse of what I need - needs to hide columns if the first three charcter in irow=1, icol=1 are same as first threchars in range cells