PDA

View Full Version : [SOLVED] Insert values before hiding column



Juriemagic
06-05-2015, 03:23 AM
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
06-05-2015, 05:45 AM
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
06-05-2015, 06:12 AM
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
06-05-2015, 06:28 AM
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
06-07-2015, 10:16 PM
Well, I'm sure I have, I will however check again and try..will let you know, thanx..

Juriemagic
06-07-2015, 11:45 PM
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!