Having Trouble Hiding Entire Column (Please Help)

[indi visual]

'Having trouble hiding the entire active column (based on these 4 criterias)


[pre]<br />
'// HIDE COLUMN IF...<br />
'' 1 if that column is not after column X<br />
'' 2 if that column is not before column E<br />
'' 3 if that column has any value<br />
'' 4 if that column is the last active column in range<br />



<br />
Private Sub Worksheet_SelectionChange(ByVal Target As Range)</p>
<p>Dim myRange As Range<br />
Dim lastActiveColumn As Range</p>
<p>Set myRange = Range("A1:Z100&#34<br />
Set lastActiveColumn = myRange.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious)</p>
<p>If Target.Column <= 25 Then<br />
If Target.Column >= 5 Then<br />
If Application.CountIf(Target.Column, "*&#34 > 0 Then<br />
If Target.Column = lastActiveColumn Then<br />
Target.EntireColumn.Hidden = True<br />
End If<br />
End If<br />
End If<br />
End If</p>
<p>End Sub<br />
[/pre]

 

[vaskov17]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim myRange As Range

Dim lastActive As Range


Set myRange = Range("A1:Z100")

Set lastActive = Target.Previous


If Target.Previous.Column <= 25 And Target.Previous.Column >= 5 Then

If Application.WorksheetFunction.CountIf(Target.Previous, "*") > 0 Then

Target.Previous.EntireColumn.Hidden = True

End If

End If

End Sub

 

[indi visual]

I tried this code for a test spin and it works only if the cell in the adjacent previous column has value.


What you helped me with is almost there, except I need it to hide the entire column if the entire previous column has anything in the entire previous column at all.


I tried modifying this line to no avail.


[pre]<br />
Application.WorksheetFunction.CountIf(Target.Column.Previous, "*&#34 > 0 Then<br />
[/pre]

 

[Luke M]

Small note, this bit:

If Target.Previous.Column <= 25

Should be

If Target.Previous.Column <= 24

for column X

 

[indi visual]

My logic (this may not coincide with what I have above my apologies).


But here is essentially what I'm looking to accomplish.


To save space on my worksheet I've hid unused columns.


However, I want to unHide those columns one at a time if the previous column is activated.


That way it always expands if necessary.


EXPAND THE COLUMNS..


once cell is activated

if the column the cell is in -is NOT before column E

if the column the cell is in -is NOT after column X

if the column the cell is in -is on the immediate left of a column that is empty

if the column the cell is in -is on the immediate right of a column that is not completely empty


Then


show the next column over on the immediate right (.EntireColumn.Hidden = false)

 

[Luke M]

Hi indi,


While it's certainly possible to setup all the criteria (which I admit, is still somewhat confusing), might I suggest using either a split window or freeze panes? Hiding/unhiding columns like that would, IMO, become very confusing and hard to work with. Users might not easily understand where there columns have gone to, or how to get back to where they want (for instance, if J:O are hidden, how do I quickly get to col L?)

 

[indi visual]

Forgetting absolutely everything I've said so far : )


Imagine walking up a staircase, and for every step your foot lands on a new step appears.


Now, imagine walking down a staircase, and for every step your foot lands on one stair behind you disappears.


This is my goal with the columns as the users activates them.


You're right. This is confusing as hell so I'm just gonna take a stab at this one solo and post the working code when I figure it out. But thanks anyway guys.