Combining IF with LEFT to hide columns VBA

danno79

New Member
Joined
Oct 28, 2009
Messages
45
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
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Hello and welcome to MrExcel.

Shouldn't it be?

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

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,835
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Oct 28, 2009
Messages
45
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

Legend
Joined
Jun 19, 2002
Messages
63,650

ADVERTISEMENT

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

danno79

New Member
Joined
Oct 28, 2009
Messages
45
Yes - pretty sure code code is in the right place as it is for a worksheet change - plus already had similar code working :-

Code:
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

Well-known Member
Joined
Apr 28, 2004
Messages
75,835
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.
Code:
? Application.EnableEvents
Should return True if events are enabled.
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
This works for me

Code:
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

New Member
Joined
Oct 28, 2009
Messages
45
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,499
Messages
5,529,217
Members
409,857
Latest member
KailuaTown
Top