Combining IF else with LEFT to hide columns

Question

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. I have the code for hiding columns if cells in a range are blank as this;-

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

And the code for identifiying the first 3 charcters of a cell;-

Dim LResult As String

LResult = Left ("Alphabet",3)

But how do I combine the two, referencing a specific cell rather than "Alphabet"?

Cant get this to work - any suggestions?

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

Cheers

Answer 1

You have almost the working code. You are comparing cell.Value to an empty string - now just apply Left to it

LResult = Left (cell.Value,3)

Edit:

row = 20
col = 30

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

where you want data from cell at row and col (I used 20, 30 as the example)