• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Almost embarrassed to post..

Gregg Wolin

Gregg Wolin

Member
I am experimenting with various UI designs for a model I have and have been learning (slowly) some VBA in the process to deal with navigation. I have been setting up various subs to hide columns based on the value of one or more inputs but whenever I move something on the sheet, the references in the code don't move (I assume it has to do with relative vs. absolute referencing). Is it possible to define a column as a range so that my macro isnt affected by rows or columns moving around in the worksheet as i refine it?

I am currently been accomplishing one such task with the following code:

If Range("E15").Value = 0 Then
Columns("P").EntireColumn.Hidden = True
Else
Columns("P").EntireColumn.Hidden = False

However, if i add or delete, any columns that precede "P", i have to go in and update the code. Can i define certain columns (or groups of columns) as a range that can be hidden so that no matter I do, the code hides the "defined" column(s)?
 
After playing with it for a little while, I can get it to hide, but it needs to be "active" insofar as the column will be hidden if the header has 1 value and visible if another.
 
Why not use Named range? It will adjust cell reference when you delete columns. And you can reference it by name in the code.

Ex:
Code: 
Sub Demo()
If [E15].Value = 0 Then
    Range("Filter_List").EntireColumn.Hidden = True
Else
    Range("Filter_List").EntireColumn.Hidden = False
End If
End Sub
 
Thanks Chihiro! That's what i was looking for. I intially tried using the Range but I didn't have the correct syntax
 
i get a Runtime error '424' Object Required when i use:
["iv_Sales.LotsP2"].EntireColumn.Hidden = ["i_Lots.P2"].Value = 0
 
Last edited by a moderator:

Double quote forbidden within this syntax
as you can see in my previous post or in VBA inner help …​
 
Marc L said:
Double quote forbidden within this syntax
as you can see in my previous post or in VBA inner help …​
Click to expand...
Well duhhh.. it works! However, it isn't running automatically when i change the number in trigger cell (I have to manually run the macro). Whats the code to make it execute when the value of the trigger cell changes?
 
You must log in or register to reply here.
Back
Top