Insert values before hiding column

[Juriemagic]

Hi good people!

The following code hides columns when a cell in row 2 goes zero. Works great. I am after an addition to the code, to have the column, of which the cell in row 2 has gone to zero, populate with the "na()" value. Now, there are 2 ranges, the one is B3:AT137, and the other CR3:CJ43. I cannot find something on the forums that would help me out, so please, if anyone could help me, it'll be awesome!!. Thanx guys..

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 Dim Cell As Range
    Set Target = Intersect(Target, Target.Parent.Rows(2))
    If Not Target Is Nothing Then
        For Each Cell In Target
            Cell.EntireColumn.Hidden = (Cell.Value = 0)
        Next Cell
    End If
End Sub

[p45cal]

I'm surprised it does work great!
Anyway, try:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Cell As Range, NARange As Range
Set Target = Intersect(Target, Target.Parent.Rows(2))
If Not Target Is Nothing Then
  For Each Cell In Target
    If Cell.Value = 0 Then
      Cell.EntireColumn.Hidden = True
      Set NARange = Intersect(Cell.EntireColumn, Range("B3:AT137,CR3:CJ43"))
      If Not NARange Is Nothing Then NARange.Formula = "=na()"
    Else
      Cell.EntireColumn.Hidden = False
    End If
  Next Cell
End If
End Sub

[Juriemagic]

Hi p45cal,

the code hides the columns still, however, the population of the rest of the columns only work for range B3:AT137. When a cell in row 2 inside range CR3:CJ43, goes to "0", the column hides but the rest of the column is not changed...Thanx for your time..

[p45cal]

Hi p45cal,

the code hides the columns still, however, the population of the rest of the columns only work for range B3:AT137. When a cell in row 2 inside range CR3:CJ43, goes to "0", the column hides but the rest of the column is not changed...Thanx for your time..

I have just double checked this and it does; have you accurately copied the macro?

[Juriemagic]

Well, I'm sure I have, I will however check again and try..will let you know, thanx..

[Juriemagic]

Hey man, got it. I made an error in my original post. I gave the second range as CR3:CJ43. Column CJ is BEFORE column CR. So, the code worked for column CR only. It should have been FJ. All is working just great now!!!..Thank you kindly p45cal!!..You have been a great help!